[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
wda.delivery_id,
mil.inventory_location_id
FROM
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
mtl_item_locations mil
WHERE wlpn.organization_id = p_org_id
AND wlpn.locator_id = mil.inventory_location_id
AND Nvl(mil.physical_location_id, mil.inventory_location_id) = p_loc_id
AND wlpn.subinventory_code = p_sub
AND wlpn.lpn_context in ( 11,12)
AND wlpn.lpn_id = wdd.lpn_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd.organization_id = p_org_id
AND wda.delivery_id IS NOT NULL
AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
SELECT COUNT(DISTINCT wlpn.outermost_lpn_id)
INTO x_total_no_of_lpns
FROM
wms_license_plate_numbers wlpn,
mtl_item_locations mil
WHERE wlpn.lpn_context in (11,12)
AND wlpn.organization_id = p_org_id
AND wlpn.subinventory_code = p_sub
AND wlpn.locator_id = mil.inventory_location_id
AND Nvl(mil.physical_location_id, mil.inventory_location_id) = p_loc_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id;
SELECT DISTINCT
milk.inventory_location_id,
nvl(milk.inventory_location_type, 3),
mil.picking_order,
mil.dropping_order,
ml.meaning
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.subinventory_code = p_sub
AND milk.concatenated_segments LIKE l_loc
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND milk.organization_id = mil.organization_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = mil.organization_id
AND wlpn.subinventory_code = mil.subinventory_code
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.picking_order, mil.dropping_order, milk.inventory_location_id;
SELECT DISTINCT
milk.inventory_location_id,
nvl(milk.inventory_location_type, 3),
mil.picking_order,
mil.dropping_order,
ml.meaning
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.subinventory_code = p_sub
AND milk.concatenated_segments LIKE l_loc
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND wlpn.lpn_context in ( 11,12)
AND wlpn.subinventory_code = mil.subinventory_code
AND wlpn.organization_id = mil.organization_id
AND wlpn.lpn_id = wdd2.lpn_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
AND wms_consolidation_pub.is_delivery_consolidated
(wda.delivery_id,
p_org_id,
p_sub,
mil.inventory_location_id) = 'Y'
ORDER BY ml.meaning, mil.picking_order, mil.dropping_order, milk.inventory_location_id;
SELECT DISTINCT
wlpn.license_plate_number,
milk.subinventory_code,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
wlpn.lpn_id,
/* Need to get LocatorId for LMS project, added by Anupam Jain*/
milk.inventory_location_id,
/* lms code end */
nvl(milk.inventory_location_type, 3),
mil.picking_order
FROM
wms_license_plate_numbers wlpn,
mtl_item_locations_kfv milk,
mtl_item_locations mil
WHERE wlpn.lpn_context in (11, 12)
AND wlpn.organization_id = p_org_id
AND wlpn.license_plate_number LIKE p_lpn
AND wlpn.outermost_lpn_id = wlpn.lpn_id
AND milk.organization_id = wlpn.organization_id
AND milk.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = mil.inventory_location_id
AND milk.organization_id = mil.organization_id
AND milk.subinventory_code = wlpn.subinventory_code
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
ORDER BY nvl(milk.inventory_location_type, 3), mil.picking_order;
SELECT DISTINCT
wnd.name Delivery,
wnd.delivery_id,
inv_shipping_transaction_pub.get_shipmethod_meaning(wnd.ship_method_code)
FROM
wsh_new_deliveries wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id
AND wnd.name LIKE p_delivery_name
AND wnd.organization_id = p_organization_id
AND wlpn.organization_id = p_organization_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
ORDER BY wnd.name;
SELECT DISTINCT
wnd.name Delivery,
wnd.delivery_id,
inv_shipping_transaction_pub.get_shipmethod_meaning(wnd.ship_method_code)
FROM wsh_new_deliveries_ob_grp_v wnd,
wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd
WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wda.delivery_id = wnd.delivery_id
AND wdd.released_status = 'Y'
AND wnd.organization_id = p_organization_id
AND wnd.name LIKE p_delivery_name
ORDER BY wnd.name;
SELECT DISTINCT
wdd.source_header_number,
wda.delivery_id,
--c.customer_name -- Commented for Bug# 4579790
party.party_name customer_name -- Added for Bug# 4579790
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
-- ra_customers c, -- Commented for Bug# 4579790
hz_parties party, -- Added for Bug# 4579790
hz_cust_accounts cust_acct, -- Added for Bug# 4579790
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd2
WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
--wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
AND wdd.organization_id = p_org_id
AND wdd.released_status IN ('Y')
AND wdd.source_header_number LIKE p_order
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = p_delivery_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wlpn.organization_id = p_org_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.lpn_id = wdd2.lpn_id
ORDER BY 1;
SELECT DISTINCT
wdd.source_header_number,
wda.delivery_id,
--c.customer_name -- Commented for Bug# 4579790
party.party_name customer_name -- Added for Bug# 4579790
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
-- ra_customers c, -- Commented for Bug# 4579790
hz_parties party, -- Added for Bug# 4579790
hz_cust_accounts cust_acct -- Added for Bug# 4579790
WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
--wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
AND wdd.organization_id = p_org_id
AND wdd.released_status IN ('Y')
AND wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wdd.source_header_number LIKE p_order
AND wda.delivery_id = p_delivery_id
ORDER BY 1;
SELECT DISTINCT
wdd.source_header_number,
wda.delivery_id,
--c.customer_name -- Commented for Bug# 4579790
party.party_name customer_name -- Added for Bug# 4579790
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
-- ra_customers c, -- Commented for Bug# 4579790
hz_parties party, -- Added for Bug# 4579790
hz_cust_accounts cust_acct, -- Added for Bug# 4579790
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd2
WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
--wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
AND wdd.organization_id = p_org_id
AND wdd.released_status IN ('Y')
AND wdd.source_header_number LIKE p_order
AND wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wlpn.organization_id = p_org_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wdd2.released_status = 'X'
AND wlpn.lpn_id = wdd2.lpn_id
ORDER BY 1;
SELECT DISTINCT
wdd.source_header_number,
wda.delivery_id,
--c.customer_name -- Commented for Bug# 4579790
party.party_name customer_name -- Added for Bug# 4579790
FROM
wsh_delivery_details_ob_grp_v wdd,
wsh_delivery_assignments_v wda,
-- ra_customers c, -- Commented for Bug# 4579790
hz_parties party, -- Added for Bug# 4579790
hz_cust_accounts cust_acct -- Added for Bug# 4579790
WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
--wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
AND wdd.organization_id = p_org_id
AND wdd.released_status IN ('Y')
AND wda.delivery_Detail_id = wdd.delivery_Detail_id
AND wdd.source_header_number LIKE p_order
ORDER BY 1;
SELECT
msi.secondary_inventory_name,
Nvl(msi.locator_type, 1),
msi.description,
msi.asset_inventory,
msi.lpn_controlled_flag,
msi.picking_order,
msi.enable_locator_alias
FROM
mtl_secondary_inventories msi
WHERE msi.organization_id = p_org_id
AND msi.lpn_controlled_flag = 1
AND msi.secondary_inventory_name LIKE p_sub
ORDER BY msi.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.alias = p_alias
AND milk.subinventory_code = p_sub
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = mil.organization_id
AND wlpn.subinventory_code = mil.subinventory_code
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.subinventory_code = p_sub
AND milk.alias = p_alias
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND wlpn.lpn_context in ( 11,12)
AND wlpn.subinventory_code = mil.subinventory_code
AND wlpn.organization_id = mil.organization_id
AND wlpn.lpn_id = wdd2.lpn_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
AND wms_consolidation_pub.is_delivery_consolidated
(wda.delivery_id,
p_org_id,
p_sub,
mil.inventory_location_id) = 'Y'
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
AND milk.subinventory_code = p_sub
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = mil.organization_id
AND wlpn.subinventory_code = mil.subinventory_code
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
wms_license_plate_numbers wlpn,
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.subinventory_code = p_sub
AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
AND mil.inventory_location_id = wlpn.locator_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND wlpn.lpn_context in ( 11,12)
AND wlpn.subinventory_code = mil.subinventory_code
AND wlpn.organization_id = mil.organization_id
AND wlpn.lpn_id = wdd2.lpn_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND wms_consolidation_pub.is_delivery_consolidated
(wda.delivery_id,
p_org_id,
p_sub,
mil.inventory_location_id) = 'Y'
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
wdd2.source_header_number
FROM
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd2,
wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.lpn_id = wdd.lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = wlpn.organization_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.released_status = 'Y';
SELECT DISTINCT
wdd2.source_header_number
FROM
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd2,
wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.lpn_id = wdd.lpn_id
AND wdd.organization_id = wlpn.organization_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.released_status = 'Y'
AND wda.delivery_id = x_delivery_id;
SELECT DISTINCT
wda.delivery_id,
wlpno.license_plate_number,
wlpn.locator_id,
inv_projectlocator_pub.get_project_number(mil.project_id),
inv_projectlocator_pub.get_task_number(mil.task_id)
INTO
x_delivery_id,
x_lpn,
l_loc_id,
x_project,
x_task
FROM
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn,
mtl_item_locations mil,
wms_license_plate_numbers wlpno
WHERE wlpn.organization_id = p_org_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpno.lpn_id = p_lpn_id -- bug 2764736
AND wlpn.lpn_id = wdd.lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = wlpn.organization_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND ROWNUM = 1;
SELECT DISTINCT
wda.delivery_id,
wlpno.license_plate_number,
wlpn.locator_id,
wlpn.subinventory_code,
INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
inv_projectlocator_pub.get_project_number(mil.project_id),
inv_projectlocator_pub.get_task_number(mil.task_id)
INTO
x_delivery_id,
x_lpn,
l_loc_id,
x_sub,
x_loc,
x_project,
x_task
FROM
wms_license_plate_numbers wlpn,
wms_license_plate_numbers wlpno,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
wsh_delivery_details wdd2,
mtl_item_locations mil
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpno.lpn_id = p_lpn_id -- bug 2764736
AND wlpn.lpn_context in ( 11, 12)
AND wlpn.organization_id = p_org_id
AND wdd.source_header_number = x_order_number
AND wdd.released_status = 'Y'
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wlpn.lpn_id = wdd2.lpn_id
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND ROWNUM = 1;
SELECT
wlpn.license_plate_number,
wlpn.locator_id,
wlpn.subinventory_code,
INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
inv_projectlocator_pub.get_project_number(mil.project_id),
inv_projectlocator_pub.get_task_number(mil.task_id)
INTO
x_lpn,
l_loc_id,
x_sub,
x_loc,
x_project,
x_task
FROM wms_license_plate_numbers wlpn,
mtl_item_locations mil
WHERE wlpn.lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND wlpn.lpn_context in ( 11,12);
SELECT
wlpn.license_plate_number,
wlpn.locator_id,
wlpn.subinventory_code,
INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
inv_projectlocator_pub.get_project_number(mil.project_id),
inv_projectlocator_pub.get_task_number(mil.task_id)
INTO
x_lpn,
l_loc_id,
x_sub,
x_loc,
x_project,
x_task
FROM wms_license_plate_numbers wlpn,
mtl_item_locations mil
WHERE wlpn.lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND wlpn.lpn_context in ( 11,12);
SELECT DISTINCT
wda.delivery_id,
wlpno.license_plate_number,
wlpn.locator_id,
wlpn.subinventory_code,
INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
inv_projectlocator_pub.get_project_number(mil.project_id),
inv_projectlocator_pub.get_task_number(mil.task_id)
INTO
x_delivery_id,
x_lpn,
l_loc_id,
x_sub,
x_loc,
x_project,
x_task
FROM
wms_license_plate_numbers wlpn,
wms_license_plate_numbers wlpno,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd,
mtl_item_locations mil
WHERE wlpn.outermost_lpn_id = p_lpn_id
AND wlpno.lpn_id = p_lpn_id -- bug 2764736
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = p_org_id
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wlpn.lpn_id = wdd.lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wlpn.locator_id = mil.inventory_location_id
AND wlpn.organization_id = mil.organization_id
AND ROWNUM = 1;
SELECT DISTINCT
wlpn.outermost_lpn_id
FROM
wms_license_plate_numbers wlpn,
mtl_item_locations_kfv milk
WHERE wlpn.lpn_context in (11,12)
AND wlpn.subinventory_code = x_sub
AND wlpn.organization_id = p_org_id
AND milk.concatenated_segments LIKE (x_loc || '%')
AND wlpn.locator_id = milk.inventory_location_id
AND wlpn.subinventory_code = milk.subinventory_code
AND milk.organization_id = wlpn.organization_id
ORDER BY wlpn.outermost_lpn_id;
SELECT DISTINCT wlpn.outermost_lpn_id
FROM
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn,
wsh_delivery_assignments_v wda,
wsh_delivery_details wdd2
WHERE wdd.source_header_number = x_order_number
AND wdd.organization_id = p_org_id
AND wdd.released_status = 'Y'
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND (Nvl(wda.delivery_id, -999) = Nvl(x_delivery_id, Nvl(wda.delivery_id, -999))
OR
x_delivery_id = 0)
AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd2.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = wdd2.organization_id
ORDER BY wlpn.outermost_lpn_id;
SELECT DISTINCT wlpn.outermost_lpn_id
FROM
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = x_delivery_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd.organization_id = p_org_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context in (11,12)
AND wlpn.organization_id = wdd.organization_id
ORDER BY wlpn.outermost_lpn_id;
SELECT wlpn.lpn_id
INTO l_lpn1_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = x_lpn
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = p_org_id;
SELECT DISTINCT wlpn.outermost_lpn_id
FROM
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = x_delivery_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wdd.organization_id = p_org_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.organization_id = wdd.organization_id
ORDER BY wlpn.outermost_lpn_id;
SELECT COUNT(DISTINCT Nvl(mil.physical_location_id,
mil.inventory_location_id))
INTO x_tot_locs_for_del
FROM mtl_item_locations mil,
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = x_delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.locator_id = mil.inventory_location_id
AND wdd.organization_id = mil.organization_id
AND wdd.released_status = 'Y'
;
SELECT COUNT(wdd.delivery_detail_id)
INTO x_tot_lines_for_del
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.organization_id = p_org_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = x_delivery_id
AND wdd.lpn_id IS NULL;
SELECT COUNT(wdd.delivery_detail_id)
INTO x_tot_comp_lines_for_del
FROM
wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.organization_id = p_org_id
AND wdd.released_status = 'Y'
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = x_delivery_id
AND wdd.lpn_id IS NULL;
SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_context = 11
AND wlpn.subinventory_code = p_from_sub
AND wlpn.locator_id = p_from_loc_id
AND wlpn.organization_id = p_org_id
AND wlpn.lpn_id = wlpn.outermost_lpn_id
AND wlpn.parent_lpn_id IS NULL;
SELECT mil.inventory_location_type
INTO l_to_loc_type
FROM mtl_item_locations mil
WHERE mil.organization_id = p_org_id
AND mil.subinventory_code = p_to_sub
AND mil.inventory_location_id = p_to_loc_id;
IF (g_trace_on = 1) THEN mydebug('exception selecting to_loc_type' ); END IF;
SELECT mtl_material_transactions_s.NEXTVAL INTO l_hdr_id FROM dual;
SELECT wlc.inventory_item_id
INTO l_item_id
FROM
wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.organization_id = wlpn.organization_id
AND wlpn.outermost_lpn_id = l_lpn_id
AND wlpn.organization_id = p_org_id
AND ROWNUM = 1;
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP
(TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
PROCESS_FLAG,
transaction_status,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSFER_SUBINVENTORY ,
TRANSFER_TO_LOCATION,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_DATE,
acct_period_id,
CONTENT_LPN_ID,
transfer_lpn_id,
posting_flag,
wms_task_type) -- bug 2879208
VALUES
(l_hdr_id,
mtl_material_transactions_s.NEXTVAL,
'Y',
3,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
l_item_id,-- inventory item id
p_org_id,
p_from_sub,
p_from_loc_id,
p_to_sub,
p_to_loc_id,
1,--trx qty
1, --prim qty
'X',--uom
2,-- p_trx_type_id,
2,-- p_trx_action_id,
13,-- p_trx_src_type_id,
sysdate, --tran date
l_period_id,
l_lpn_id,--content lpn id
p_transfer_lpn_id, -- transfer lpn id
'Y',
7) -- bug 2879208
returning transaction_temp_id INTO l_temp_id;
IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: transaction_temp_id just inserted: ' || l_temp_id);
SELECT DISTINCT
msi.secondary_inventory_name,
Nvl(msi.locator_type, 1),
msi.description,
msi.asset_inventory,
msi.picking_order,
msi.enable_locator_alias
FROM
mtl_secondary_inventories msi,
mtl_item_locations mil
WHERE msi.organization_id = p_org_id
AND msi.lpn_controlled_flag = 1
AND msi.secondary_inventory_name LIKE p_sub
AND msi.secondary_inventory_name = mil.subinventory_code
AND mil.organization_id = msi.organization_id
AND Nvl(mil.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND inv_material_status_grp.is_status_applicable('Y',
NULL,
2,
NULL,
NULL,
p_org_id,
NULL,
msi.secondary_inventory_name,
NULL,
NULL,
NULL,
'Z') = 'Y'
ORDER BY msi.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml,
wms_license_plate_numbers wlpn
WHERE milk.organization_id = p_org_id
AND milk.alias = p_alias
AND milk.subinventory_code = p_sub
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND wlpn.lpn_context = 11
AND wlpn.organization_id = mil.organization_id
AND wlpn.subinventory_code = mil.subinventory_code
AND wlpn.locator_id = mil.inventory_location_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND NOT exists
(
SELECT 1
FROM
wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_context <> 11
AND wlpn2.organization_id = mil.organization_id
AND wlpn2.subinventory_code = mil.subinventory_code
AND wlpn2.locator_id = mil.inventory_location_id
)
AND NOT exists
(
SELECT 1
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.primary_transaction_quantity > 0
AND moqd.locator_id = mil.inventory_location_id
AND moqd.organization_id = mil.organization_id
AND moqd.lpn_id IS NULL
)
AND inv_material_status_grp.is_status_applicable('Y',
NULL,
2,
NULL,
NULL,
p_org_id,
NULL,
milk.subinventory_code,
milk.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.alias = p_alias
AND milk.subinventory_code = p_sub
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND NOT (milk.subinventory_code = p_from_sub
AND
milk.concatenated_segments LIKE p_from_loc ||'%')
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND NOT exists
(
SELECT 1
FROM
wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_context <> 11
AND wlpn2.organization_id = mil.organization_id
AND wlpn2.subinventory_code = mil.subinventory_code
AND wlpn2.locator_id = mil.inventory_location_id
)
AND inv_material_status_grp.is_status_applicable('Y',
NULL,
2,
NULL,
NULL,
p_org_id,
NULL,
milk.subinventory_code,
milk.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml,
wms_license_plate_numbers wlpn
WHERE milk.organization_id = p_org_id
AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
AND milk.subinventory_code = p_sub
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND wlpn.lpn_context = 11
AND wlpn.organization_id = mil.organization_id
AND wlpn.subinventory_code = mil.subinventory_code
AND wlpn.locator_id = mil.inventory_location_id
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND NOT exists
(
SELECT 1
FROM
wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_context <> 11
AND wlpn2.organization_id = mil.organization_id
AND wlpn2.subinventory_code = mil.subinventory_code
AND wlpn2.locator_id = mil.inventory_location_id
)
AND NOT exists
(
SELECT 1
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.primary_transaction_quantity > 0
AND moqd.locator_id = mil.inventory_location_id
AND moqd.organization_id = mil.organization_id
AND moqd.lpn_id IS NULL
)
AND inv_material_status_grp.is_status_applicable('Y',
NULL,
2,
NULL,
NULL,
p_org_id,
NULL,
milk.subinventory_code,
milk.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY ml.meaning, mil.picking_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.picking_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
AND milk.subinventory_code = p_sub
AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND NOT (milk.subinventory_code = p_from_sub
AND
milk.concatenated_segments LIKE p_from_loc ||'%')
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
AND NOT exists
(
SELECT 1
FROM
wms_license_plate_numbers wlpn2
WHERE wlpn2.lpn_context <> 11
AND wlpn2.organization_id = mil.organization_id
AND wlpn2.subinventory_code = mil.subinventory_code
AND wlpn2.locator_id = mil.inventory_location_id
)
AND inv_material_status_grp.is_status_applicable('Y',
NULL,
2,
NULL,
NULL,
p_org_id,
NULL,
milk.subinventory_code,
milk.inventory_location_id,
NULL,
NULL,
'L') = 'Y'
ORDER BY ml.meaning, mil.picking_order;
SELECT lpn_id, license_plate_number, inventory_location_id,
concatenated_segments, subinventory_code, lpn_context
FROM (
SELECT DISTINCT
wlpn.lpn_id,
wlpn.license_plate_number,
milk.inventory_location_id,
inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.subinventory_code,
11 lpn_context
FROM
wms_license_plate_numbers wlpn,
mtl_item_locations_kfv milk
WHERE wlpn.license_plate_number LIKE p_lpn
AND wlpn.locator_id <> p_from_loc_id -- Make sure that we don't choose the TO lpn FROM the FROM locator
AND milk.organization_id = p_org_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND wlpn.lpn_context = 11 -- Picked
AND wlpn.organization_id = milk.organization_id
AND wlpn.locator_id = milk.inventory_location_id
UNION ALL
SELECT DISTINCT
wlpn.lpn_id,
wlpn.license_plate_number,
NULL,
NULL,
NULL,
5 lpn_context
FROM
wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number LIKE p_lpn
AND wlpn.lpn_context = 5 -- Defined but not used
AND wlpn.organization_id = p_org_id)
ORDER BY license_plate_number;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.dropping_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
AND milk.subinventory_code = p_sub
AND milk.inventory_location_id = mil.inventory_location_id
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND nvl(mil.empty_flag, 'Y') = 'Y'
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.dropping_order;
SELECT DISTINCT
milk.inventory_location_id,
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
milk.description,
ml.meaning,
mil.dropping_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.alias = p_alias
AND milk.subinventory_code = p_sub
AND milk.inventory_location_id = mil.inventory_location_id
AND milk.organization_id = mil.organization_id
AND milk.project_id IS NULL
AND milk.task_id IS NULL
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND nvl(mil.empty_flag, 'Y') = 'Y'
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.dropping_order;
SELECT DISTINCT
INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
ml.meaning,
mil.dropping_order
FROM
mtl_item_locations_kfv milk,
mtl_item_locations mil,
mfg_lookups ml
WHERE milk.organization_id = p_org_id
AND milk.subinventory_code = p_sub
AND milk.inventory_location_id = mil.inventory_location_id
AND milk.organization_id = mil.organization_id
AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
g_loc_type_packing_station,
g_loc_type_staging_lane)
AND nvl(mil.empty_flag, 'Y') = 'Y'
AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
ORDER BY ml.meaning, mil.dropping_order;
SELECT 1
INTO l_count
FROM
wms_license_plate_numbers wlpn,
-- wms_dispatched_tasks_history wdth, Commented by Bug#4337112.
wsh_delivery_details wdd2,
wsh_delivery_assignments_v wda
WHERE wda.delivery_id = p_delivery_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.lpn_id = wlpn.lpn_id
AND wlpn.lpn_context in ( 11,12)
AND wlpn.subinventory_code = Nvl(p_sub, wlpn.subinventory_code) -- added NVL for packing workbench query (patchset J)
AND wlpn.locator_id = Nvl(p_loc_id, wlpn.locator_id) -- added NVL for packing workbench query (patchset J)
AND wlpn.organization_id = p_org_id
-- AND wlpn.lpn_id = wdth.transfer_lpn_id Commented by Bug#4337112.
AND NOT exists
(
SELECT 1
FROM
mtl_material_transactions_temp mmtt,
wsh_delivery_details wdd1,
wsh_delivery_assignments_v wda2
WHERE wda2.delivery_id = p_delivery_id
AND wda2.delivery_detail_id = wdd1.delivery_detail_id
AND wdd1.organization_id = p_org_id
AND wdd1.released_status = 'S'
AND wdd1.move_order_line_id = mmtt.move_order_line_id
AND wdd1.organization_id = mmtt.organization_id
-- AND wdth.operation_plan_id = mmtt.operation_plan_id Commented by Bug#4337112.
);
SELECT mmtt.transaction_temp_id
INTO l_temp_id
FROM
wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id
AND ROWNUM = 1;
SELECT
wdth.suggested_dest_subinventory,
wdth.suggested_dest_locator_id,
wdth.operation_plan_id,
wlpn.subinventory_code,
wlpn.locator_id
INTO
l_orig_sub,
l_orig_loc,
l_op_plan_id,
l_from_sub,
l_from_loc
FROM
wms_dispatched_tasks_history wdth,
wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wdth.organization_id = p_org_id
AND wdth.transfer_lpn_id = wlpn.lpn_id
AND wdth.task_type = WMS_GLOBALS.G_WMS_TASK_TYPE_PICK --Bug5883610
AND ROWNUM < 2;
SELECT wlpn.subinventory_code, wlpn.locator_id
INTO l_from_sub, l_from_loc
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id; --Bug#4337112. Changed outermost_lpn_id to lpn_id
SELECT wlc.inventory_item_id
INTO l_item_id
FROM
wms_lpn_contents wlc,
wms_license_plate_numbers wlpn
WHERE wlc.parent_lpn_id = wlpn.lpn_id
AND wlc.organization_id = wlpn.organization_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND ROWNUM = 1;
INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP
(TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
PROCESS_FLAG,
transaction_status,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
transfer_organization,
TRANSFER_SUBINVENTORY ,
TRANSFER_TO_LOCATION,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_DATE,
acct_period_id,
CONTENT_LPN_ID,
posting_flag,
operation_plan_id,
wms_task_type,
transfer_lpn_id)
VALUES
(mtl_material_transactions_s.nextval,
mtl_material_transactions_s.NEXTVAL,
'Y',
2,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_user_id,
l_item_id,-- inventory item id
p_org_id,
l_from_sub,
l_from_loc,
p_org_id,
l_orig_sub,
l_orig_loc,
1,--trx qty
1, --prim qty
'X',--uom
2,-- p_trx_type_id,
2,-- p_trx_action_id,
13,-- p_trx_src_type_id,
sysdate, --tran date
l_period_id,
p_lpn_id,--content lpn id
'Y',
l_op_plan_id,
l_task_type,
p_lpn_id)
returning transaction_temp_id INTO p_temp_id;
INSERT INTO WMS_DISPATCHED_TASKS
(TASK_ID ,
TRANSACTION_TEMP_ID ,
ORGANIZATION_ID ,
USER_TASK_TYPE ,
PERSON_ID ,
EFFECTIVE_START_DATE ,
EFFECTIVE_END_DATE ,
EQUIPMENT_ID ,
EQUIPMENT_INSTANCE ,
PERSON_RESOURCE_ID ,
MACHINE_RESOURCE_ID ,
STATUS ,
DISPATCHED_TIME ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
task_type,
suggested_dest_subinventory,
suggested_dest_locator_id,
operation_plan_id,
TRANSFER_LPN_ID)
VALUES(wms_dispatched_tasks_s.nextval,
p_temp_id,
p_org_id,
2,
p_emp_id,
sysdate,
sysdate,
0,
p_eqp_ins,
0,
0,
4,
sysdate,
sysdate,
p_user_id,
sysdate,
p_user_id,
l_task_type,
l_orig_sub,
l_orig_loc,
l_op_plan_id,
p_lpn_id
);
mydebug('Update lpn_context to packing context - p_lpn_id: ' || p_lpn_id
|| ':' || wms_container_pvt.lpn_loaded_in_stage);
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
WHERE lpn_id = p_lpn_id;