[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln
, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
AND (p_planning_org_id IS NULL
OR moqd.planning_organization_id = p_planning_org_id)
AND (p_planning_tp_type IS NULL
OR moqd.planning_tp_type = p_planning_tp_type)
AND (p_owning_org_id IS NULL
OR moqd.owning_organization_id = p_owning_org_id)
AND (p_owning_tp_type IS NULL
OR moqd.owning_tp_type = p_owning_tp_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, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
-- Bug 5018199
UNION
SELECT DISTINCT mln.parent_lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln
, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.parent_lot_number IS NOT NULL
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
AND (p_planning_org_id IS NULL
OR moqd.planning_organization_id = p_planning_org_id)
AND (p_planning_tp_type IS NULL
OR moqd.planning_tp_type = p_planning_tp_type)
AND (p_owning_org_id IS NULL
OR moqd.owning_organization_id = p_owning_org_id)
AND (p_owning_tp_type IS NULL
OR moqd.owning_tp_type = p_owning_tp_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, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln
, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moqd
WHERE moqd.lot_number = mln.lot_number
AND moqd.inventory_item_id = mln.inventory_item_id
AND moqd.organization_id = mln.organization_id
AND ((moqd.containerized_flag = 1 AND p_lpn_id IS NOT NULL)
OR (moqd.containerized_flag = 2 AND p_lpn_id IS NULL))
AND moqd.subinventory_code = p_subinventory_code
AND NVL(moqd.locator_id, -1) = NVL(NVL(p_locator_id, moqd.locator_id), -1)
AND (p_planning_org_id IS NULL
OR moqd.planning_organization_id = p_planning_org_id)
AND (p_planning_tp_type IS NULL
OR moqd.planning_tp_type = p_planning_tp_type)
AND (p_owning_org_id IS NULL
OR moqd.owning_organization_id = p_owning_org_id)
AND (p_owning_tp_type IS NULL
OR moqd.owning_tp_type = p_owning_tp_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, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND NVL(mln.disable_flag,'2') = '2' --Bug#4108798 Disabled lots must not be displayed
AND NVL(mln.expiration_date,sysdate +1 ) > sysdate -- Expired lots must not be displayed . -- Bug#5360600 - Items with null expiration date should be displayed.
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, wms_asn_details wad,
rcv_shipment_headers rsh, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.lot_number = wad.lot_number_expected
AND mln.inventory_item_id = wad.item_id
AND mln.organization_id = wad.organization_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND wad.organization_id = p_organization_id
AND wad.discrepancy_reporting_context = 'O'
AND wad.item_id = p_item_id
AND wad.shipment_num = rsh.shipment_num
AND rsh.shipment_header_id = p_source_header_id
--Bug5726837:Added the following union to take care of direct delivery cases
--when there is no data present in wms_asn_details and moqd.
UNION
SELECT rls.lot_num lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, rcv_lots_supply rls,
rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = p_source_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = p_item_id
AND rls.supply_type_code = 'SHIPMENT'
AND mln.inventory_item_id = p_item_id
AND rls.lot_num = mln.lot_number
AND rls.lot_num LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL,p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , lot_number, NULL, 'O') = 'Y'
GROUP BY rls.lot_num, mln.description, mln.expiration_date, mmst.status_code
HAVING SUM(rls.primary_quantity) > 0;
SELECT msik.lot_control_code
INTO l_lot_control_code
FROM mtl_system_items_kfv msik, rcv_shipment_lines rsl
WHERE msik.inventory_item_id = p_item_id
AND msik.inventory_item_id = rsl.item_id --Bug 4235750
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.from_organization_id = msik.organization_id
AND Nvl(rsl.asn_lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rsl.asn_lpn_id,-1)) --Bug 6908946
AND rownum < 2; --Bug 4235750
SELECT rls.lot_num lot_number
, mlnv.description
, mlnv.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mlnv, rcv_lots_supply rls,
rcv_shipment_lines rsl, mtl_material_statuses_tl mmst
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = p_item_id
AND Nvl(rsl.asn_lpn_id,-1) = Nvl(decode(p_from_lpn_id,0,NULL,p_from_lpn_id),Nvl(rsl.asn_lpn_id,-1)) --Bug 6908946
AND mlnv.organization_id = rsl.from_organization_id
AND mlnv.inventory_item_id = p_item_id
AND rls.lot_num = mlnv.lot_number
AND rls.lot_num LIKE (p_lot_number)
AND rls.SUPPLY_TYPE_CODE = 'SHIPMENT' --Bug 6908946
AND mlnv.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , lot_number, NULL, 'O') = 'Y'
GROUP BY rls.lot_num, mlnv.description, mlnv.expiration_date, mmst.status_code
HAVING SUM(rls.quantity) > 0;
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND exists ( SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE mln.lot_number = moq.lot_number
AND moq.organization_id = p_organization_id
AND moq.lpn_id IS NULL -- added for bug 4614645
AND NVL(moq.revision, '@') = NVL(p_revision, NVL(moq.revision, '@'))
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -999) = NVL(p_locator_id, NVL(moq.locator_id, -999))
AND moq.inventory_item_id = p_item_id)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
PROCEDURE get_cgupdate_lot_lov(
x_lot_num_lov OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_from_cost_group_id IN NUMBER
, p_lot_number IN VARCHAR2
) IS
BEGIN
IF p_lpn_id IS NULL THEN
OPEN x_lot_num_lov FOR
SELECT DISTINCT moq.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_onhand_quantities_detail moq,
mtl_material_statuses_tl mmst
WHERE mln.lot_number = moq.lot_number
AND mln.inventory_item_id = p_inventory_item_id
AND mln.organization_id = p_organization_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, p_subinventory_code , p_locator_id , moq.lot_number, NULL, 'O') = 'Y'
AND moq.lot_number LIKE (p_lot_number)
AND (moq.cost_group_id = p_from_cost_group_id
OR p_from_cost_group_id IS NULL
)
AND (moq.revision = p_revision
OR (moq.revision IS NULL
AND p_revision IS NULL
)
)
AND moq.containerized_flag = 2
AND moq.inventory_item_id = p_inventory_item_id
AND moq.locator_id = p_locator_id
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
ORDER BY moq.lot_number;
SELECT DISTINCT wlc.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
mtl_material_statuses_tl mmst
WHERE mln.lot_number = wlc.lot_number
AND mln.inventory_item_id = p_inventory_item_id
AND mln.organization_id = p_organization_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, NULL, NULL, wlc.lot_number, NULL, 'O') = 'Y'
AND wlc.lot_number LIKE (p_lot_number)
AND (wlc.cost_group_id = p_from_cost_group_id
OR p_from_cost_group_id IS NULL
)
AND (wlc.revision = p_revision
OR (wlc.revision IS NULL
AND p_revision IS NULL
)
)
AND wlc.inventory_item_id = p_inventory_item_id
AND wlc.parent_lpn_id = p_lpn_id
ORDER BY wlc.lot_number;
END get_cgupdate_lot_lov;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number);
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number)
ORDER BY mln.lot_number;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number >= p_from_lot_number
AND mln.lot_number LIKE (p_lot_number)
ORDER BY mln.lot_number;
SELECT reason_name
, description
, reason_id
FROM mtl_transaction_reasons
WHERE reason_name LIKE (p_reason)
AND nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE ;
SELECT reason_name
, description
, reason_id
FROM mtl_transaction_reasons
WHERE reason_name LIKE (p_reason)
AND nvl(DISABLE_DATE,SYSDATE+1) > SYSDATE
-- nsrivast, invconv , transaction reason security
AND ( NVL ( fnd_profile.value_wnps('INV_TRANS_REASON_SECURITY'), 'N') = 'N'
OR
reason_id IN (SELECT reason_id FROM mtl_trans_reason_security mtrs
WHERE(( responsibility_id = fnd_global.resp_id OR NVL(responsibility_id, -1) = -1 )
AND
( mtrs.transaction_type_id = p_txn_type_id OR NVL(mtrs.transaction_type_id, -1) = -1 )
)-- where ends
)-- select ends
) -- and condn ends ,-- nsrivast, invconv
ORDER BY REASON_NAME
;
SELECT DISTINCT org.organization_id
, org.organization_code
, org.organization_name
FROM org_organization_definitions org, mtl_system_items msi
WHERE org.organization_id <> p_from_organization_id
AND org.organization_id = msi.organization_id
AND msi.inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items
WHERE organization_id = p_from_organization_id)
AND org.organization_code LIKE (p_to_organization_code);
SELECT a.to_organization_id
, b.organization_code
, c.NAME
, a.intransit_type
FROM mtl_interorg_parameters a, mtl_parameters b, hr_all_organization_units c
WHERE a.from_organization_id = p_from_organization_id
AND a.to_organization_id = b.organization_id
AND a.to_organization_id = c.organization_id
AND a.internal_order_required_flag = 2
AND b.organization_code LIKE (p_to_organization_code)
ORDER BY 2;
/* SELECT DISTINCT organization_id
, organization_code
, organization_name
, 0
FROM org_organization_definitions
WHERE organization_code LIKE (p_organization_code)
ORDER BY 2; */
SELECT distinct mp.organization_id
, mp.organization_code
, hu.name organization_name
,0
FROM mtl_parameters mp
, hr_organization_units hu
WHERE mp.organization_code LIKE (p_organization_code || '%')
and mp.organization_id = hu.organization_id order by 2;
/* SELECT cost_group
, cost_group_id
, description
FROM cst_cost_groups
WHERE cost_group LIKE (p_cost_group); */
select ccg.cost_group ,ccga.cost_group_id, ccg.description
from cst_cost_groups ccg, cst_cost_group_accounts ccga
where ccg.cost_group_id = ccga.cost_group_id
and ccga.organization_id = nvl(p_organization_id, ccga.organization_id)
--WHERE organization_id = p_organization_id
and ccg.cost_group LIKE (p_cost_group);
PROCEDURE get_cgupdate_cost_group(
x_cost_group_lov OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_revision IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_from_cost_group_id IN NUMBER
, p_from_cost_group IN VARCHAR2
, p_to_cost_group IN VARCHAR2
) IS
BEGIN
IF p_from_cost_group_id IS NULL THEN
IF p_lpn_id IS NULL THEN
OPEN x_cost_group_lov FOR
SELECT ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
WHERE ccg.cost_group LIKE (p_from_cost_group)
AND ccg.cost_group_id = moq.cost_group_id
AND ccg.cost_group_type <> 1 --PJM-WMS Integration
AND ((moq.revision = p_revision)
OR (moq.revision IS NULL
AND p_revision IS NULL
)
)
AND moq.containerized_flag = 2
AND moq.inventory_item_id = p_inventory_item_id
AND moq.locator_id = p_locator_id
AND moq.subinventory_code = p_subinventory_code
AND moq.organization_id = p_organization_id
GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
ORDER BY ccg.cost_group;
SELECT ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, wms_lpn_contents wlc
WHERE ccg.cost_group LIKE (p_from_cost_group)
AND ccg.cost_group_id = wlc.cost_group_id
AND ccg.cost_group_type <> 1 --PJM-WMS Integration
AND ((wlc.revision = p_revision)
OR (wlc.revision IS NULL
AND p_revision IS NULL
)
)
AND wlc.inventory_item_id = p_inventory_item_id
AND wlc.parent_lpn_id = p_lpn_id
GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
ORDER BY ccg.cost_group;
SELECT ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, cst_cost_group_accounts ccga
WHERE ccg.cost_group LIKE (p_to_cost_group)
AND ccg.cost_group_id = ccga.cost_group_id
AND ccga.cost_group_id <> p_from_cost_group_id
AND ccg.cost_group_type <> 1 --PJM-WMS Integration
AND ccga.organization_id = p_organization_id
GROUP BY ccg.cost_group, ccg.cost_group_id, ccg.description
ORDER BY ccg.cost_group;
END get_cgupdate_cost_group;
SELECT cost_group
, cost_group_id
, description
FROM cst_cost_groups
WHERE organization_id = p_organization_id
AND cost_group LIKE (p_cost_group);
SELECT UNIQUE ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, mtl_physical_inventory_tags mpit
WHERE ccg.organization_id = p_organization_id
AND ccg.cost_group LIKE (p_cost_group)
AND ccg.cost_group_id = mpit.cost_group_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND mpit.subinventory = p_subinventory
AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
AND mpit.inventory_item_id = p_inventory_item_id
AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
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 cost_group
, cost_group_id
, description
FROM cst_cost_groups
WHERE organization_id = p_organization_id
AND cost_group LIKE (p_cost_group);
SELECT UNIQUE ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, mtl_cycle_count_entries mcce
WHERE ccg.organization_id = p_organization_id
AND ccg.cost_group LIKE (p_cost_group)
AND ccg.cost_group_id = mcce.cost_group_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.organization_id = p_organization_id
AND mcce.subinventory = p_subinventory
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
AND mcce.inventory_item_id = p_inventory_item_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3);
SELECT transaction_type_id
, transaction_type_name
, description
, transaction_action_id
FROM mtl_transaction_types
WHERE transaction_action_id = p_transaction_action_id
AND transaction_source_type_id = p_transaction_source_type_id
AND transaction_type_name LIKE (p_transaction_type_name)
AND transaction_type_id NOT IN (66, 67, 68);
SELECT transaction_type_id
, transaction_type_name
, description
, transaction_action_id
FROM mtl_transaction_types
WHERE transaction_action_id = p_transaction_action_id
AND transaction_source_type_id = p_transaction_source_type_id
AND transaction_type_name LIKE (p_transaction_type_name);
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_locator_id , p_subinventory_code , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
ORDER BY lot_number, concatenated_segments;
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
ORDER BY 1, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
ORDER BY lot_number, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id
AND msik.default_lot_status_id IS NOT NULL
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND msik.lot_translate_enabled = 'Y'
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number LN
, mln.inventory_item_id
, msik.concatenated_segments cs
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND msik.lot_translate_enabled = 'Y'
ORDER BY lot_number, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number LN
, mln.inventory_item_id
, msik.concatenated_segments cs
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
ORDER BY lot_number, concatenated_segments;
SELECT allow_different_status INTO l_allow_different_status FROM mtl_parameters WHERE organization_id = p_organization_id;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_split_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
ORDER BY lot_number, concatenated_segments;
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') =
'Y'
UNION ALL
SELECT DISTINCT moq.lot_number
, moq.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
WHERE moq.organization_id = p_organization_id
AND moq.lot_number IS NOT NULL
AND moq.organization_id = mil.organization_id
AND moq.organization_id = mln.organization_id
AND moq.organization_id = msik.organization_id
AND mil.segment19 = p_project_id
AND (mil.segment20 = p_task_id
OR (mil.segment20 IS NULL
AND p_task_id IS NULL
)
)
AND mln.lot_number = moq.lot_number
AND msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') =
'Y'
ORDER BY 1, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, (select status_code from mtl_material_statuses_vl where status_id = mln.status_id) status_code
, mln.status_id -- Bug#2347381
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND mln.inventory_item_id LIKE l_inventory_item_id
AND msik.lot_merge_enabled = 'Y'
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id LIKE (l_status_id) -- restrict to lot_status
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
ORDER BY lot_number, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id
AND msik.default_lot_status_id IS NOT NULL
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND msik.lot_translate_enabled = 'Y'
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number LN
, mln.inventory_item_id
, msik.concatenated_segments cs
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND msik.lot_translate_enabled = 'Y'
ORDER BY lot_number, concatenated_segments;
SELECT mln.lot_number lot_number
, mln.inventory_item_id
, msik.concatenated_segments concatenated_segments
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, mms.status_code
, mms.status_id
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
UNION ALL
SELECT mln.lot_number LN
, mln.inventory_item_id
, msik.concatenated_segments cs
, msik.description
, TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
, NULL status_code
, msik.default_lot_status_id -- Bug#2267947
FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
AND mln.organization_id = p_organization_id
AND mln.organization_id = msik.organization_id
AND mln.inventory_item_id = msik.inventory_item_id
AND msik.lot_control_code = 2
AND mln.inventory_item_id LIKE l_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
ORDER BY lot_number, concatenated_segments;
SELECT distribution_account
, disposition_id
, concatenated_segments
FROM mtl_generic_dispositions_kfv
WHERE organization_id = p_organization_id
AND ((concatenated_segments LIKE ('%'|| p_description))
OR (concatenated_segments IS NULL
AND p_description IS NULL
)
OR (concatenated_segments IS NULL
AND p_description = '%'
)
)
AND enabled_flag = 'Y'
AND NVL(effective_date, SYSDATE - 1) <= SYSDATE
AND NVL(disable_date, SYSDATE + 1) > SYSDATE
ORDER BY concatenated_segments;
SELECT a.code_combination_id
, a.concatenated_segments
, a.chart_of_accounts_id
FROM gl_code_combinations_kfv a, org_organization_definitions b
WHERE b.organization_id = p_organization_id
AND a.chart_of_accounts_id = b.chart_of_accounts_id
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.enabled_flag = 'Y'
AND NVL(a.start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(a.end_date_active, SYSDATE + 1) > SYSDATE
AND a.SUMMARY_FLAG in ('N') -- Bug 3792738
AND a.DETAIL_POSTING_ALLOWED NOT IN ('N') -- Bug 3792738
ORDER BY a.concatenated_segments;
SELECT DISTINCT a.code_combination_id
, a.concatenated_segments
FROM gl_code_combinations_kfv a, org_organization_definitions b, mtl_txn_request_lines c
WHERE c.header_id = p_moheader_id
AND b.organization_id = p_organization_id
AND a.chart_of_accounts_id = b.chart_of_accounts_id
AND c.to_account_id = a.code_combination_id
AND a.concatenated_segments LIKE (p_concatenated_segments)
AND a.enabled_flag = 'Y'
AND NVL(a.start_date_active, SYSDATE - 1) <= SYSDATE
AND NVL(a.end_date_active, SYSDATE + 1) > SYSDATE;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number);
SELECT UNIQUE mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_physical_inventory_tags mpit,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.lot_number = mpit.lot_number
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mpit.inventory_item_id = p_inventory_item_id
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.subinventory = p_subinventory_code
AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mpit.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
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 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 NVL(serial_count_option, 1)
INTO l_serial_count_option
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id
AND organization_id = p_organization_id;
SELECT NVL(serial_number_control_code, 1)
INTO l_serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number)
-- Bug# 2770853
-- Honor the lot material status for cycle count adjustment transaction
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
'Y',
NULL,
p_organization_id,
p_inventory_item_id,
p_subinventory_code ,
p_locator_id ,
mln.lot_number,
NULL,
'O',
p_parent_lpn_id) = 'Y'); /*Bug 6889528-Added p_parent_lpn_id to the call*/
SELECT UNIQUE mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_cycle_count_entries mcce,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_inventory_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number)
AND mln.lot_number = mcce.lot_number
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
-- The sub and loc have to match an existing cycle count entry
-- OR the entry contains an LPN and
-- container discrepancies are allowed
-- OR the item is serial controlled, the cycle count header allows
-- serial items and serial discrepancies are allowed
AND ((mcce.subinventory = p_subinventory_code
AND NVL(mcce.locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR (mcce.parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
OR (l_serial_count_option <> 1
AND l_serial_number_control_code NOT IN (1, 6)
AND l_serial_discrepancy_option = 1
)
)
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
-- Bug# 2770853
-- Honor the lot material status for cycle count adjustment transaction
AND (INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
'Y',
NULL,
p_organization_id,
p_inventory_item_id,
p_subinventory_code ,
p_locator_id ,
mln.lot_number,
NULL,
'O',
p_parent_lpn_id) = 'Y'); /*Bug 6889528-Added p_parent_lpn_id to the call*/
SELECT a.lot_number lot_number
, a.description description
, a.expiration_date expiration_date
, mmst.status_code status_code
, SUM(Decode(Nvl(p_uom_code,uom_code),
uom_code,
b.quantity - Nvl(b.quantity_delivered,0),
inv_convert.inv_um_convert(
p_item_id
,NULL
,b.quantity - Nvl(b.quantity_delivered,0)
,uom_code
,p_uom_code
,NULL
,NULL)
)
) quantity
FROM mtl_lot_numbers a, mtl_txn_request_lines b,
mtl_material_statuses_tl mmst
WHERE b.organization_id = p_organization_id
AND b.inventory_item_id = p_item_id
AND b.lpn_id = p_lpn_id
AND b.lot_number LIKE (p_lot_number)
AND b.inspection_status = 1 -- To be inspected yet
AND Nvl(b.wms_process_flag,1) <> 2 --Don't pick up those that has been processed
AND b.inventory_item_id = a.inventory_item_id
AND b.organization_id = a.organization_id
AND a.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND b.lot_number = a.lot_number
AND b.line_status = 7
AND b.quantity - Nvl(b.quantity_delivered,0) > 0
GROUP BY a.lot_number,a.description,a.expiration_date,mmst.status_code;
SELECT DISTINCT wlc.lot_number
, mln.description
, mln.expiration_date
, '0'
, '0' --wlc.quantity
FROM mtl_lot_numbers mln, wms_lpn_contents wlc
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.parent_lpn_id, '0') = NVL(p_lpn_id, NVL(wlc.parent_lpn_id, '0'))
AND mln.inventory_item_id = wlc.inventory_item_id
AND mln.lot_number = wlc.lot_number
AND mln.organization_id = wlc.organization_id
AND wlc.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT wlc.lot_number
, mln.description
, mln.expiration_date
, '0'
, wlc.quantity
FROM mtl_lot_numbers mln, wms_lpn_contents wlc
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.parent_lpn_id, '0') = NVL(p_lpn_id, NVL(wlc.parent_lpn_id, '0'))
AND mln.inventory_item_id = wlc.inventory_item_id
AND mln.lot_number = wlc.lot_number
AND mln.organization_id = wlc.organization_id
AND wlc.lot_number LIKE (p_lot_number)
AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, 501, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id, mln.lot_number, NULL, 'O') = 'Y')
AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, 500, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code, p_locator_id, mln.lot_number, NULL, 'O') = 'Y');
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number);
SELECT DISTINCT ccg.cost_group
, ccg.cost_group_id
, ccg.description
FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
WHERE ccg.cost_group_id = moq.cost_group_id
AND ccg.cost_group_type = 3
AND NVL(ccg.organization_id, moq.organization_id) = moq.organization_id
AND NVL(moq.subinventory_code, '@') = NVL(p_subinventory_code, NVL(moq.subinventory_code, '@'))
AND NVL(moq.locator_id, -999) = NVL(TO_NUMBER(p_locator_id), NVL(moq.locator_id, -999))
AND moq.inventory_item_id = NVL(TO_NUMBER(p_inventory_item_id), moq.inventory_item_id)
AND moq.organization_id = p_organization_id
AND ccg.cost_group LIKE (p_cost_group);
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code NOT IN (9)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code IN (1, 2, 6, 7, 8, 10)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code IN (1, 2, 6, 7, 8, 10)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_BUSINESS_FLOW'
AND lookup_code NOT IN (3)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT meaning
, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_BUSINESS_FLOW'
AND lookup_code IN (1, 2, 3, 8, 9, 13, 14, 15, 17, 21, 23, 24, 26, 31, 32, 33)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT p_all_label_str meaning, 0 lookup_code
FROM DUAL
WHERE p_all_label_str LIKE (p_lookup_type)
UNION ALL
SELECT meaning, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT p_all_label_str meaning, 0 lookup_code
FROM DUAL
WHERE p_all_label_str LIKE (p_lookup_type)
UNION ALL
SELECT meaning, lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WMS_LABEL_TYPE'
AND lookup_code NOT IN (3,4,5,9)
AND meaning LIKE (p_lookup_type)
ORDER BY lookup_code;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number);
SELECT negative_inv_receipt_code
INTO l_negative_rcpt_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, mtlt.primary_quantity
, mtlt.transaction_quantity
FROM mtl_lot_numbers mln, mtl_transaction_lots_temp mtlt,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, mtlt.primary_quantity
, mtlt.transaction_quantity
FROM mtl_lot_numbers mln,
mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND moq.containerized_flag = 1
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND wlc.parent_lpn_id = p_lpn_id
AND wlc.lot_number = mln.lot_number
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_organization_id
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, mtlt.primary_quantity
, mtlt.transaction_quantity
FROM mtl_lot_numbers mln,
mtl_transaction_lots_temp mtlt, mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND moq.containerized_flag = 2
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y';
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, mtlt.primary_quantity
, mtlt.transaction_quantity
FROM mtl_lot_numbers mln,
mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND moq.containerized_flag = 1
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND wlc.parent_lpn_id = p_lpn_id
AND wlc.lot_number = mln.lot_number
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_organization_id
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y';
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, sum(mag.primary_quantity)
, sum(mag.transaction_quantity)
FROM mtl_lot_numbers mln, wms_allocations_gtmp mag,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mag.lot_number = mln.lot_number
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
GROUP BY mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
HAVING sum(mag.transaction_quantity) > 0;
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
, sum(mag.primary_quantity)
, sum(mag.transaction_quantity)
FROM mtl_lot_numbers mln,
mtl_transaction_lots_temp mtlt, wms_allocations_gtmp mag,
mtl_material_statuses_tl mmst
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE (p_lot_number)
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND exists (SELECT '1' FROM mtl_onhand_quantities_detail moq
WHERE moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND moq.containerized_flag = 1
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1))
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND mag.lot_number = mln.lot_number
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, mln.lot_number, NULL, 'O') = 'Y'
GROUP BY mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
HAVING sum(mag.transaction_quantity) > 0;
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mstl.status_code
FROM mtl_lot_numbers mln, wms_lpn_contents wlpnc, mtl_material_statuses_b mstb, mtl_material_statuses_tl mstl
WHERE wlpnc.parent_lpn_id = p_lpn_id
AND wlpnc.organization_id = p_org_id
AND wlpnc.inventory_item_id = p_item_id
AND ((wlpnc.revision = p_revision
AND p_revision IS NOT NULL
)
OR (p_revision IS NULL
AND wlpnc.revision IS NULL
)
)
AND wlpnc.source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND wlpnc.lot_number LIKE (p_lot_number)
AND mln.lot_number = wlpnc.lot_number
AND mln.organization_id = wlpnc.organization_id
AND mln.inventory_item_id = wlpnc.inventory_item_id
AND mln.status_id = mstb.status_id(+)
AND mstb.status_id = mstl.status_id(+)
AND mstl.LANGUAGE(+) = USERENV('LANG');
SELECT lot_number
, ' '
, ' '
, ' '
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id;
SELECT label_format_id
, label_format_name
, Decode(label_entity_type,1,'Label Set', 'Format')
FROM wms_label_formats
WHERE document_id = p_label_type_id
AND NVL(format_disable_date, SYSDATE + 1) > SYSDATE --Bug #3452076
AND label_format_name like (p_format_name)
ORDER BY label_format_name;
SELECT printer_name
, printer_type
FROM fnd_printer
WHERE printer_name LIKE (p_printer_name)
ORDER BY printer_name;
SELECT schedule_number
, organization_id
FROM wip_flow_schedules
WHERE organization_id = NVL(p_organization_id, organization_id)
AND schedule_number >= NVL(p_from_schedule_number, 0)
AND schedule_number LIKE (p_schedule_number);
SELECT msik.lot_control_code
, rsl.from_organization_id
INTO x_lot_control_code
, x_from_org_id
FROM mtl_system_items_kfv msik, rcv_shipment_lines rsl
WHERE msik.inventory_item_id = p_item_id
AND rsl.shipment_header_id = p_shipment_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.from_organization_id = msik.organization_id
AND ROWNUM < 2;
SELECT DISTINCT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, wms_lpn_contents wlc,
mtl_material_statuses_tl mmst
WHERE wlc.organization_id = p_organization_id
AND wlc.inventory_item_id = p_item_id
AND wlc.parent_lpn_id = p_lpn_id
AND mln.inventory_item_id = wlc.inventory_item_id
AND mln.lot_number = wlc.lot_number
AND mln.organization_id = wlc.organization_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND wlc.lot_number LIKE (p_lot_number)
AND inv_material_status_grp.is_status_applicable('TRUE',
NULL,
INV_GLOBALS.G_TYPE_CONTAINER_UNPACK,
NULL,
NULL,
p_organization_id,
p_item_id,
p_subinventory_code,
p_locator_id,
mln.lot_number,
NULL,
'O') = 'Y'
ORDER BY mln.lot_number;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , mtl_onhand_quantities_detail moqd
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number)
AND moqd.lot_number = mln.lot_number
AND moqd.lpn_id is NULL
ORDER BY mln.lot_number;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
WHERE wlpn.license_plate_number = p_lpn
AND wlc.parent_lpn_id = wlpn.lpn_id
AND mln.lot_number = wlc.lot_number
AND mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number LIKE (p_lot_number)
ORDER BY mln.lot_number;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst ,mtl_onhand_quantities_detail moqd
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number >= p_from_lot_number
AND mln.lot_number LIKE (p_lot_number)
AND moqd.lot_number = mln.lot_number
AND moqd.lpn_id is NULL
ORDER BY mln.lot_number;
SELECT mln.lot_number
, mln.description
, mln.expiration_date
, mmst.status_code
FROM mtl_lot_numbers mln, mtl_material_statuses_tl mmst , wms_license_plate_numbers wlpn , wms_lpn_contents wlc
WHERE wlpn.license_plate_number = p_lpn
AND wlc.parent_lpn_id = wlpn.lpn_id
AND mln.lot_number = wlc.lot_number
AND mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.status_id = mmst.status_id (+)
AND mmst.language (+) = userenv('LANG')
AND mln.lot_number >= p_from_lot_number
AND mln.lot_number LIKE (p_lot_number)
ORDER BY mln.lot_number;