[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
INTO l_locator_rec
FROM
mtl_item_locations
WHERE inventory_location_id = x_locator_id;
SELECT inventory_location_id
INTO l_locator_id
FROM
mtl_item_locations
WHERE physical_location_id = x_locator_id
AND project_id = p_project_id
AND task_id = p_task_id
AND ROWNUM < 2;
SELECT *
INTO l_organization_id
FROM mtl_parameters
WHERE organization_id = l_locator_rec.organization_id;
SELECT *
INTO l_subinventory_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_locator_rec.subinventory_code
AND organization_id = l_locator_rec.organization_id;
SELECT mol.carton_grouping_id,
wda.delivery_id,
mmtt.transaction_temp_id,
mmtt.operation_plan_id,
mil.project_id,
mil.task_id
FROM
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil
WHERE
mmtt.transaction_temp_id = p_task_id AND
mmtt.move_order_line_id = mol.line_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
mmtt.transfer_to_location = mil.inventory_location_id AND
mmtt.transfer_organization = mil.organization_id AND
wdd.released_status = 'S' AND
wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
p_call_mode <> 3
UNION ALL
SELECT mol.carton_grouping_id,
wda.delivery_id,
mmtt.transaction_temp_id,
mmtt.operation_plan_id,
mil.project_id,
mil.task_id
FROM
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil
WHERE
mmtt.transaction_temp_id = p_task_id AND
mmtt.move_order_line_id = mol.line_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
pmmtt.locator_id = mil.inventory_location_id AND
pmmtt.organization_id = mil.organization_id AND
wdd.released_status = 'S' AND
wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
pmmtt.transaction_temp_id = mmtt.parent_line_id AND
p_call_mode = 3
;
SELECT wdd.subinventory del_subinventory,
wdd.locator_id del_locator_id,
nvl(mil.inventory_location_type, 3) del_locator_type,
wdth.creation_date
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth
WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND
wdd.released_status = 'Y' AND
wdd.locator_id = mil.inventory_location_id AND
nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
wdd.move_order_line_id = wdth.move_order_line_id AND
wdth.operation_plan_id = v_operation_plan_id AND
wda.delivery_id = v_delivery_id
UNION ALL -- bug 4017457
SELECT mmtt.transfer_subinventory del_subinventory,
mmtt.transfer_to_location del_locator_id,
Nvl(mil.inventory_location_type, 3) del_locator_type,
Sysdate creation_date
FROM mtl_material_transactions_temp mmtt,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mmtt.operation_plan_id = v_operation_plan_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wdd.released_status = 'S' AND
wda.delivery_id = v_delivery_id AND
mmtt.transfer_to_location = mil.inventory_location_id AND
mmtt.transfer_organization = mil.organization_id AND
Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
mmtt.transfer_to_location IS NOT NULL AND
mmtt.transfer_subinventory IS NOT NULL AND
p_call_mode <> 3
UNION ALL -- bug 4017457
SELECT pmmtt.subinventory_code del_subinventory,
pmmtt.locator_id del_locator_id,
Nvl(mil.inventory_location_type, 3) del_locator_type,
Sysdate creation_date
FROM mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mmtt.operation_plan_id = v_operation_plan_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wdd.released_status = 'S' AND
wda.delivery_id = v_delivery_id AND
pmmtt.locator_id = mil.inventory_location_id AND
pmmtt.organization_id = mil.organization_id AND
Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
pmmtt.locator_id IS NOT NULL AND
pmmtt.subinventory_code IS NOT NULL AND
pmmtt.transaction_temp_id = mmtt.parent_line_id AND
p_call_mode = 3
ORDER BY 4 DESC
;
SELECT wdd.subinventory mol_subinventory,
wdd.locator_id mol_locator_id,
nvl(mil.inventory_location_type, 3) mol_locator_type,
wdth.creation_date
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_txn_request_lines mol,
mtl_item_locations mil,
mtl_secondary_inventories msi,
wms_dispatched_tasks_history wdth
WHERE
mol.line_id = wdd.move_order_line_id AND
wdd.released_status = 'Y' AND
wdd.locator_id = mil.inventory_location_id AND
wda.delivery_detail_id = wdd.delivery_detail_id AND
wda.delivery_id IS NULL AND -- bug 2768678
nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
wdd.move_order_line_id = wdth.move_order_line_id AND
wdth.operation_plan_id = v_operation_plan_id AND
mol.carton_grouping_id = v_carton_grouping_id
UNION ALL -- bug 4017457
SELECT mmtt.transfer_subinventory mol_subinventory,
mmtt.transfer_to_location mol_locator_id,
Nvl(mil.inventory_location_type, 3) mol_locator_type,
Sysdate creation_date
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mmtt.operation_plan_id = v_operation_plan_id AND
mmtt.move_order_line_id = mol.line_id AND
mol.carton_grouping_id = v_carton_grouping_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id IS NULL AND
wdd.released_status = 'S' AND
mmtt.transfer_to_location = mil.inventory_location_id AND
mmtt.transfer_organization = mil.organization_id AND
Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
Nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
mmtt.transfer_to_location IS NOT NULL AND
mmtt.transfer_subinventory IS NOT NULL AND
p_call_mode <> 3
UNION ALL -- bug 4017457
SELECT pmmtt.subinventory_code mol_subinventory,
pmmtt.locator_id mol_locator_id,
Nvl(mil.inventory_location_type, 3) mol_locator_type,
Sysdate creation_date
FROM mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mmtt.operation_plan_id = v_operation_plan_id AND
mmtt.move_order_line_id = mol.line_id AND
mol.carton_grouping_id = v_carton_grouping_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
wda.delivery_id IS NULL AND
wdd.released_status = 'S' AND
pmmtt.locator_id = mil.inventory_location_id AND
pmmtt.organization_id = mil.organization_id AND
Nvl(mil.inventory_location_type, 3) = G_LOC_TYPE_CONSOLIDATION AND
Nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
Nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
pmmtt.locator_id IS NOT NULL AND
pmmtt.subinventory_code IS NOT NULL AND
pmmtt.transaction_temp_id = mmtt.parent_line_id AND
p_call_mode = 3
ORDER BY 4 DESC
;
SELECT mil.inventory_location_id locator_id,
mil.subinventory_code subinventory_code,
mil.dropping_order,
mil.picking_order
FROM mtl_item_locations mil,
mtl_secondary_inventories msi,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_task_id
AND mil.subinventory_code = mmtt.transfer_subinventory
AND mil.organization_id = mmtt.organization_id
AND nvl(mil.inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND Nvl(mil.empty_flag, 'Y') = 'Y'
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
-- bug 4017457
AND NOT exists (SELECT 1
FROM mtl_material_transactions_temp mmtt2,
wsh_delivery_details wdd
WHERE mmtt2.move_order_line_id = wdd.move_order_line_id AND
wdd.released_status = 'S' AND
mmtt2.transfer_organization = mil.organization_id AND
mmtt2.transfer_subinventory = mil.subinventory_code AND
mmtt2.transfer_to_location = mil.inventory_location_id)
AND NOT exists (SELECT 1
FROM mtl_material_transactions_temp mmtt3,
mtl_material_transactions_temp pmmtt2,
wsh_delivery_details wdd
WHERE mmtt3.move_order_line_id = wdd.move_order_line_id AND
wdd.released_status = 'S' AND
pmmtt2.transaction_temp_id = mmtt3.parent_line_id AND
pmmtt2.organization_id = mil.organization_id AND
pmmtt2.subinventory_code = mil.subinventory_code AND
pmmtt2.locator_id = mil.inventory_location_id)
AND p_call_mode <> 3
UNION ALL
SELECT mil.inventory_location_id locator_id,
mil.subinventory_code subinventory_code,
mil.dropping_order,
mil.picking_order
FROM mtl_item_locations mil,
mtl_secondary_inventories msi,
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt
WHERE mmtt.transaction_temp_id = p_task_id
AND mil.subinventory_code = pmmtt.subinventory_code
AND mil.organization_id = mmtt.organization_id
AND nvl(mil.inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND Nvl(mil.empty_flag, 'Y') = 'Y'
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
AND pmmtt.transaction_temp_id = mmtt.parent_line_id
-- bug 4017457
AND NOT exists (SELECT 1
FROM mtl_material_transactions_temp mmtt2,
wsh_delivery_details wdd
WHERE mmtt2.move_order_line_id = wdd.move_order_line_id AND
wdd.released_status = 'S' AND
mmtt2.transfer_organization = mil.organization_id AND
mmtt2.transfer_subinventory = mil.subinventory_code AND
mmtt2.transfer_to_location = mil.inventory_location_id)
AND NOT exists (SELECT 1
FROM mtl_material_transactions_temp mmtt3,
mtl_material_transactions_temp pmmtt2,
wsh_delivery_details wdd
WHERE mmtt3.move_order_line_id = wdd.move_order_line_id AND
wdd.released_status = 'S' AND
pmmtt2.transaction_temp_id = mmtt3.parent_line_id AND
pmmtt2.organization_id = mil.organization_id AND
pmmtt2.subinventory_code = mil.subinventory_code AND
pmmtt2.locator_id = mil.inventory_location_id)
AND p_call_mode = 3
ORDER BY 3,4;
IF p_call_mode in (1,3) THEN -- locator selection
IF p_task_type IS NULL OR p_task_id IS NULL THEN
x_return_status := g_ret_sts_unexp_error;
print_debug('Invalid input: For locator selection p_task_type and p_task_id cannot be NULL.', 4);
print_debug('Invalid input: For locator selection p_locator_id, p_task_type and p_task_id cannot be NULL.', 4);
SELECT
mmtt.transfer_subinventory,
mmtt.transfer_to_location
INTO
l_pick_release_subinventory,
l_pick_release_locator_id
FROM
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_task_id;
SELECT 1
INTO l_cons_loc_exists_flag
FROM mtl_item_locations mil,
mtl_secondary_inventories msi,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_task_id
AND mil.subinventory_code = mmtt.transfer_subinventory
AND mil.organization_id = mmtt.organization_id
AND nvl(inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
;
SELECT 1
INTO l_cons_loc_exists_flag
FROM mtl_item_locations mil,
mtl_secondary_inventories msi,
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt
WHERE mmtt.transaction_temp_id = p_task_id
AND pmmtt.transaction_temp_id = mmtt.parent_line_id
AND mil.subinventory_code = pmmtt.subinventory_code
AND mil.organization_id = mmtt.organization_id
AND nvl(inventory_location_type, 3)= G_LOC_TYPE_CONSOLIDATION -- consolidation locator
AND nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate)
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
AND nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate)
;
SELECT Nvl(mil.inventory_location_type, 3),
mil.subinventory_code,
Nvl(mil.empty_flag, 'Y'),
Nvl(mil.disable_date, trunc(sysdate+1)),
Nvl(msi.disable_date, trunc(sysdate+1))
INTO l_validate_loc_type,
l_validate_loc_subinventory,
l_validate_loc_empty_flag,
l_loc_disable_date,
l_sub_disable_date
FROM mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mil.inventory_location_id = p_locator_id
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
;
SELECT mol.carton_grouping_id,
wda.delivery_id,
mmtt.transaction_temp_id,
-- mmtt.operation_plan_id,
mil.project_id,
mil.task_id,
mil.organization_id
FROM
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd1,
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda,
mtl_item_locations mil
WHERE
mmtt.transaction_temp_id = p_task_id AND
wdd1.move_order_line_id = mol.line_id AND
mmtt.content_lpn_id = wdd2.lpn_id AND
mmtt.transfer_to_location = mil.inventory_location_id AND
mmtt.transfer_organization = mil.organization_id AND
wdd1.released_status = 'Y' AND
wdd1.delivery_detail_id = wda.delivery_detail_id AND
wdd2.delivery_detail_id = wda.parent_delivery_detail_id AND
p_call_mode <> 3 AND
ROWNUM < 2
UNION ALL
SELECT mol.carton_grouping_id,
wda.delivery_id,
mmtt.transaction_temp_id,
-- mmtt.operation_plan_id,
mil.project_id,
mil.task_id,
mil.organization_id
FROM
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt,
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil
WHERE
mmtt.transaction_temp_id = p_task_id AND
mmtt.move_order_line_id = mol.line_id AND
mmtt.move_order_line_id = wdd.move_order_line_id AND
pmmtt.locator_id = mil.inventory_location_id AND
pmmtt.organization_id = mil.organization_id AND
wdd.released_status = 'S' AND
wdd.delivery_detail_id = wda.delivery_detail_id (+) AND
pmmtt.transaction_temp_id = mmtt.parent_line_id AND
p_call_mode = 3 AND
ROWNUM < 2
;
SELECT mol.carton_grouping_id,
wda.delivery_id,
NULL,
-- mmtt.operation_plan_id,
mol.project_id,
mol.task_id,
mol.organization_id
FROM
mtl_txn_request_lines mol,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE
mol.line_id = p_mol_id AND
wdd.move_order_line_id = mol.line_id AND
wdd.released_status = 'S' AND
wdd.delivery_detail_id = wda.delivery_detail_id AND
ROWNUM < 2
;
SELECT wdd.subinventory del_subinventory,
wdd.locator_id del_locator_id,
nvl(mil.inventory_location_type, 3) del_locator_type
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE wda.delivery_detail_id = wdd.delivery_detail_id AND
wdd.released_status = 'Y' AND
wdd.locator_id = mil.inventory_location_id AND
(nvl(mil.inventory_location_type, g_loc_type_storage_loc) = G_LOC_TYPE_STAGING_LANE
OR(nvl(mil.inventory_location_type, g_loc_type_storage_loc) IN (g_loc_type_staging_lane, g_loc_type_consolidation, g_loc_type_packing_station) AND
p_call_mode = 2)
) AND
-- wdd.move_order_line_id = wdth.move_order_line_id AND
-- wdth.operation_plan_id = v_operation_plan_id AND
nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
wda.delivery_id = v_delivery_id
ORDER BY wdd.last_update_date DESC
;
SELECT wdd.subinventory mol_subinventory,
wdd.locator_id mol_locator_id,
nvl(mil.inventory_location_type, 3) mol_locator_type
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_txn_request_lines mol,
mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE
mol.line_id = wdd.move_order_line_id AND
wdd.released_status = 'Y' AND
wdd.locator_id = mil.inventory_location_id AND
wda.delivery_detail_id = wdd.delivery_detail_id AND
wda.delivery_id IS NULL AND -- bug 2768678
(nvl(mil.inventory_location_type, g_loc_type_storage_loc) = G_LOC_TYPE_STAGING_LANE
OR(nvl(mil.inventory_location_type, g_loc_type_storage_loc) IN (g_loc_type_staging_lane, g_loc_type_consolidation, g_loc_type_packing_station) AND
p_call_mode = 2)
) AND
-- wdd.move_order_line_id = wdth.move_order_line_id AND
-- wdth.operation_plan_id = v_operation_plan_id AND
nvl(mil.disable_date, trunc(sysdate+1)) > trunc(sysdate) AND
msi.secondary_inventory_name = mil.subinventory_code AND
msi.organization_id = mil.organization_id AND
nvl(msi.disable_date, trunc(sysdate + 1)) > trunc(sysdate) AND
mol.carton_grouping_id = v_carton_grouping_id
ORDER BY wdd.last_update_date DESC
;
SELECT wts.STOP_ID
FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_legs wdl
WHERE wdl.delivery_id = v_del_id AND
wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = wt.trip_id;
IF p_call_mode IN (1,3) THEN -- locator selection
IF p_task_type IS NULL OR (p_task_id IS NULL AND p_mol_id IS NULL) THEN
x_return_status := g_ret_sts_unexp_error;
print_debug('Invalid input: For locator selection p_task_type and p_task_id cannot be NULL.', 4);
print_debug('Invalid input: For locator selection p_locator_id, p_task_type and p_task_id cannot be NULL.', 4);
SELECT
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
nvl(mil.inventory_location_type, 3)
INTO
l_pick_release_subinventory,
l_pick_release_locator_id,
l_pick_release_locator_type
FROM
mtl_material_transactions_temp mmtt,
mtl_item_locations mil
WHERE mmtt.transaction_temp_id = p_task_id
AND mil.inventory_location_id = mmtt.transfer_to_location
AND mil.organization_id = mmtt.organization_id
;
SELECT
pmmtt.subinventory_code,
pmmtt.locator_id,
nvl(mil.inventory_location_type, 3)
INTO
l_pick_release_subinventory,
l_pick_release_locator_id,
l_pick_release_locator_type
FROM
mtl_material_transactions_temp mmtt,
mtl_material_transactions_temp pmmtt,
mtl_item_locations mil
WHERE mmtt.transaction_temp_id = p_task_id
AND mmtt.parent_line_id = pmmtt.transaction_temp_id
AND mil.inventory_location_id = pmmtt.locator_id
AND mil.organization_id = pmmtt.organization_id
;
SELECT Nvl(mil.inventory_location_type, 3),
mil.subinventory_code,
Nvl(mil.empty_flag, 'Y'),
Nvl(mil.disable_date, trunc(sysdate+1)),
Nvl(msi.disable_date, trunc(sysdate+1))
INTO l_validate_loc_type,
l_validate_loc_subinventory,
l_validate_loc_empty_flag,
l_loc_disable_date,
l_sub_disable_date
FROM mtl_item_locations mil,
mtl_secondary_inventories msi
WHERE mil.inventory_location_id = p_locator_id
AND msi.secondary_inventory_name = mil.subinventory_code
AND msi.organization_id = mil.organization_id
;
SELECT Nvl(p_sug_sub,Nvl(mmtt.transfer_subinventory, mmtt.subinventory_code)),
Nvl(p_sug_loc,Nvl(mmtt.transfer_to_location, mmtt.locator_id)),
mmtt.organization_id,
wda.delivery_id
INTO l_to_sub_code,
l_to_loc_id,
l_organization_id,
l_delivery_id
FROM mtl_material_transactions_temp mmtt,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE mmtt.transaction_temp_id = p_task_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.released_status = 'S'
AND wdd.delivery_detail_id = wda.delivery_detail_id;
SELECT lpn_id
INTO x_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = l_license_plan_number;