[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((current_organization_id = p_organization_id
AND current_status = 1
)
OR (current_status = 4 AND
Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2
)
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
ORDER BY LPAD(serial_number, 20);
select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
into l_enforce_rma_sn
from RCV_PARAMETERS
where organization_id = p_organization_id;
For c_rma_line in ( select line_id
FROM
OE_ORDER_LINES_all OEL,
OE_ORDER_HEADERS_all OEH
WHERE OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.INVENTORY_ITEM_ID = p_item_id
AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
AND OEL.HEADER_ID = OEH.HEADER_ID
AND OEH.HEADER_ID = p_oe_order_header_id
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
)
Loop
INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
p_api_version => 0.9
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_errorcode => l_errorcode
, p_rma_line_id => c_rma_line.LINE_ID
, p_org_id => P_ORGANIZATION_ID
, p_item_id => p_item_id
);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND msn.serial_number LIKE (p_serial)
AND EXISTS ( select 'x' from mtl_rma_serial_temp msrt
where msrt.organization_id = p_organization_id
and msrt.inventory_item_id = p_item_id
and msrt.serial_number = msn.serial_number
and msrt.serial_number LIKE (p_serial)
)
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
SELECT msi1.lot_control_code src_lot_ctrl
, msi1.serial_number_control_code src_srl_ctrl
, msi2.lot_control_code rcv_lot_ctrl
, rsl.source_document_code
INTO l_src_org_lot_ctrl
, l_src_org_srl_ctrl
, l_rcv_org_lot_ctrl
, l_source_document_code
FROM mtl_system_items msi1
, mtl_system_items msi2
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = p_shipment_header_id
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = p_item_id
AND msi1.inventory_item_id = p_item_id
AND msi1.organization_id = rsl.from_organization_id
AND msi1.inventory_item_id = msi2.inventory_item_id
AND msi2.organization_id = p_organization_id
AND ROWNUM=1;
SELECT msn.serial_number
, ''
, 0
, rss.lot_num
, 0
, msn.current_status
, mms.status_code
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
, mtl_serial_numbers msn
, mtl_material_statuses_tl mms
WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
--BUG 3417870: The RSL.shipment_line_status_code will be FULLY
-- RECEIVED, so we need to comment it out.
-- AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
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 msn.inventory_item_id = p_item_id
AND msn.serial_number = rss.serial_num
AND msn.current_status = 5
AND Nvl(msn.lpn_id,NVL(p_from_lpn_id,-1)) = NVL(p_from_lpn_id, NVL(msn.lpn_id, -1))
AND ( (l_rcv_org_lot_ctrl = 1 OR l_src_org_lot_ctrl = 1) OR
((l_rcv_org_lot_ctrl = 2 AND l_src_org_lot_ctrl = 2) AND
(Nvl(rss.lot_num,'@@@') = Nvl(p_lot_num,'@@@')))
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, NULL
, msn.serial_number
, 'S') = 'Y'
AND msn.serial_number LIKE (p_serial)
ORDER BY LPAD(msn.serial_number, 20);
SELECT msn.serial_number
, ''
, 0
, p_lot_num
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn
, rcv_shipment_lines rsl
, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND rsl.shipment_header_id = p_shipment_header_id
AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
AND ( ( msn.current_status IN (1, 6)
AND msn.current_organization_id = p_organization_id
) OR
( msn.current_status = 4
AND nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2
) )
AND rsl.shipment_line_status_code <> 'FULLY RECEIVED'
AND rsl.to_organization_id = p_organization_id
AND rsl.item_id = p_item_id
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, NULL
, msn.serial_number
, 'S') = 'Y'
AND msn.serial_number LIKE (p_serial)
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
OR group_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
where a.transaction_temp_id = p_group_mark_id)
OR group_mark_id = p_group_mark_id
)
AND (line_mark_id IS NULL
OR line_mark_id = -1
OR line_mark_id in (select a.serial_transaction_temp_id from mtl_transaction_lots_temp a
where a.transaction_temp_id = p_group_mark_id)
OR line_mark_id = p_group_mark_id)
AND current_organization_id = p_organization_id
AND current_status = 3
AND current_subinventory_code = p_subinv_code
AND msn.lpn_id IS NULL
AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
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_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT status_id
INTO l_number
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT NVL(status_code, '')
INTO x_lot_status
FROM mtl_material_statuses_tl mms
WHERE mms.status_id = NVL(l_number, p_default_lot_status_id)
AND mms.language = userenv('LANG');
SELECT expiration_date
, NVL(status_code, '')
INTO x_expiration_date
, x_lot_status
FROM mtl_lot_numbers_all_v
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number
AND ROWNUM < 2
ORDER BY expiration_date;
SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
INTO x_expiration_date
FROM DUAL;
SELECT NVL(status_code, '')
INTO x_lot_status
FROM mtl_material_statuses_tl mms
WHERE mms.status_id = p_default_lot_status_id
AND mms.language = userenv('LANG');
SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
INTO x_expiration_date
FROM DUAL;
SELECT msn.current_status
, NVL(mms.status_code, '')
INTO x_current_status
, x_serial_status
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.serial_number = p_serial
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG');
SELECT msn.current_status
INTO x_current_status
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = p_item_id
AND msn.serial_number = p_serial;
SELECT NVL(mms.status_code, '')
INTO x_serial_status
FROM mtl_material_statuses_tl mms
WHERE mms.status_id = p_default_serial_status
AND mms.language (+) = userenv('LANG');
SELECT a.serial_number
, a.current_subinventory_code
, a.current_locator_id
, a.lot_number
, b.expiration_date
, a.current_status
, mms.status_code
, inv_project.get_locsegs(a.current_locator_id, p_current_organization_id)
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
WHERE a.current_organization_id = p_current_organization_id
AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
AND a.inventory_item_id = p_inventory_item_id
AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
AND ((a.revision = p_revision)
OR (a.revision IS NULL AND p_revision IS NULL))
AND a.current_status = 3
AND b.inventory_item_id(+) = a.inventory_item_id
AND b.organization_id(+) = a.current_organization_id
AND b.lot_number(+) = a.lot_number
AND mms.status_id(+) = a.status_id
AND mms.language (+) = userenv('LANG')
AND a.serial_number LIKE (p_serial_number)
AND (p_planning_org_id IS NULL
OR planning_organization_id = p_planning_org_id)
AND (p_planning_tp_type IS NULL
OR planning_tp_type = p_planning_tp_type)
AND (p_owning_org_id IS NULL
OR owning_organization_id = p_owning_org_id)
AND (p_owning_tp_type IS NULL
OR owning_tp_type = p_owning_tp_type)
AND a.serial_number LIKE (p_serial_number)
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_current_organization_id
, p_inventory_item_id
, a.current_subinventory_code
, a.current_locator_id
, a.lot_number
, a.serial_number
, 'A'
) = 'Y' -- modified by mxgupta because we want to check all statuses (lot and serial)
ORDER BY a.serial_number;
SELECT a.serial_number
, a.current_subinventory_code
, NVL(a.current_locator_id, -1)
, a.lot_number
, b.expiration_date
, a.current_status
, mms.status_code
, inv_project.get_locsegs(a.current_locator_id, p_current_organization_id)
, inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM mtl_serial_numbers a, mtl_lot_numbers b, mtl_material_statuses_tl mms
WHERE a.current_organization_id = p_current_organization_id
AND NVL(a.lpn_id, -1) = NVL(p_lpn_id, -1)
AND a.inventory_item_id = p_inventory_item_id
AND a.current_subinventory_code = p_current_subinventory_code
AND (a.group_mark_id IS NULL OR a.group_mark_id = -1)
AND a.current_status = 3
AND mms.status_id(+) = a.status_id
AND mms.language (+) = userenv('LANG')
AND ((a.revision = p_revision)
OR (a.revision IS NULL AND p_revision IS NULL))
AND ((a.current_locator_id = p_current_locator_id)
OR (a.current_locator_id IS NULL
AND (p_current_locator_id IS NULL OR p_current_locator_id = -1))) -- Bug2564817
AND b.inventory_item_id(+) = a.inventory_item_id
AND b.organization_id(+) = a.current_organization_id
AND b.lot_number(+) = a.lot_number
AND (p_planning_org_id IS NULL
OR planning_organization_id = p_planning_org_id)
AND (p_planning_tp_type IS NULL
OR planning_tp_type = p_planning_tp_type)
AND (p_owning_org_id IS NULL
OR owning_organization_id = p_owning_org_id)
AND (p_owning_tp_type IS NULL
OR owning_tp_type = p_owning_tp_type)
AND a.serial_number LIKE (p_serial_number)
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_current_organization_id
, p_inventory_item_id
, p_current_subinventory_code
, a.current_locator_id
, a.lot_number
, a.serial_number
, 'S'
) = 'Y'
ORDER BY a.serial_number;
SELECT cost_group
, cost_group_id
, description
FROM cst_cost_groups
WHERE NVL(organization_id, p_organization_id) = p_organization_id
AND cost_group_type = 3
AND cost_group LIKE (p_cost_group)
AND cost_group_id IN (SELECT cost_group_id
FROM mtl_onhand_quantities_detail moq
WHERE organization_id = p_organization_id
AND NVL(subinventory_code, '@') = NVL(p_subinventory_code, NVL(subinventory_code, '@'))
AND NVL(locator_id, -999) = NVL(TO_NUMBER(p_locator_id), NVL(locator_id, -999))
AND inventory_item_id = NVL(TO_NUMBER(p_inventory_item_id), inventory_item_id));
SELECT serial_number
,current_subinventory_code
,current_locator_id
,lot_number
,0
,current_status
,status_code
FROM
(
SELECT serial_number
,current_subinventory_code
,current_locator_id
,lot_number
,0
,current_status
,mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((current_organization_id = p_organization_id
AND current_status IN (1, 3, 4, 6)
)
OR current_status = 5
)
AND msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
OR current_status IN (1, 6)
) --newly generated
AND serial_number LIKE (p_serial_number)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
UNION
SELECT serial_number
,current_subinventory_code
,current_locator_id
,lot_number
,0
,current_status
,mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL OR group_mark_id = -1)
AND ((current_organization_id = p_organization_id
AND current_status =1
)
OR current_status = 5
)
AND serial_number LIKE (p_serial_number)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
) ORDER BY SERIAL_NUMBER;
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_inventory_item_id
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number LIKE (p_serial_number)
AND msn.serial_number = mpit.serial_num
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.inventory_item_id = p_inventory_item_id
AND mpit.organization_id = p_organization_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)
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
,current_subinventory_code
,current_locator_id
,lot_number
,0
,current_status
,status_code
FROM (SELECT serial_number, current_subinventory_code, current_locator_id,
lot_number, 0, current_status, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL OR group_mark_id = -1)
AND (( current_organization_id = p_organization_id
AND current_status IN (1, 3, 4, 6)
)
OR current_status = 5
)
AND msn.current_subinventory_code = p_subinventory_code
AND NVL (msn.current_locator_id, -99999) = NVL (p_locator_id,-99999)
AND (NVL (msn.lot_number, '###') = NVL (p_lot_number, '###')
OR current_status IN (1, 6)
) --newly generated
AND serial_number LIKE (p_to_serial_number)
AND serial_number LIKE (l_prefix || '%')
AND msn.status_id = mms.status_id(+)
AND mms.LANGUAGE(+) = USERENV ('LANG')
AND serial_number > p_from_serial_number
-- Bug# 2770853. Honor the serial material status for physical inventory adjustments.
AND inv_material_status_grp.is_status_applicable
(NULL,
NULL,
8,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S'
) = 'Y'
UNION
SELECT serial_number, current_subinventory_code, current_locator_id,
lot_number, 0, current_status, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL OR group_mark_id = -1)
AND ( ( current_organization_id = p_organization_id
AND current_status = 1
)
OR current_status = 5
)
AND serial_number LIKE (p_to_serial_number)
AND serial_number LIKE (l_prefix || '%')
AND msn.status_id = mms.status_id(+)
AND mms.LANGUAGE(+) = USERENV ('LANG')
AND serial_number > p_from_serial_number
-- Bug# 2770853. Honor the serial material status for physical inventory adjustments.
AND inv_material_status_grp.is_status_applicable
(NULL,
NULL,
8,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S'
) = 'Y'
) ORDER BY serial_number;
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_inventory_item_id
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number LIKE (p_to_serial_number)
AND msn.serial_number LIKE (l_prefix || '%')
AND msn.serial_number = mpit.serial_num
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.inventory_item_id = p_inventory_item_id
AND mpit.organization_id = p_organization_id
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.serial_number > p_from_serial_number
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)
-- Bug# 2770853
-- Honor the serial material status for physical inventory adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
8,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.inventory_item_id
, msik.concatenated_segments
, msn.revision
, msn.lot_number
, msn.lpn_id
, wlpn.license_plate_number
, msn.current_status
, msik.primary_uom_code
FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
WHERE (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3, 4, 6)
)
OR msn.current_status = 5
)
AND msn.serial_number LIKE (p_serial_number)
AND msn.inventory_item_id = msik.inventory_item_id
AND msn.current_organization_id = msik.organization_id
AND wlpn.lpn_id(+) = msn.lpn_id
-- Bug# 2770853
-- Honor the serial material status for physical inventory adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
8,
NULL,
'Y',
p_organization_id,
msn.inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.inventory_item_id
, msik.concatenated_segments
, msn.revision
, msn.lot_number
, msn.lpn_id
, wlpn.license_plate_number
, msn.current_status
, msik.primary_uom_code
FROM mtl_serial_numbers msn, mtl_physical_inventory_tags mpit, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
WHERE (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND msn.current_organization_id = p_organization_id
AND msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
AND msn.serial_number LIKE (p_serial_number)
AND msn.serial_number = mpit.serial_num
AND msn.inventory_item_id = mpit.inventory_item_id
AND NVL(msn.lpn_id, -99999) = NVL(mpit.parent_lpn_id, -99999)
AND mpit.physical_inventory_id = p_physical_inventory_id
AND mpit.organization_id = p_organization_id
AND mpit.subinventory = p_subinventory_code
AND NVL(mpit.locator_id, -99999) = NVL(p_locator_id, -99999)
AND NVL(mpit.void_flag, 2) = 2
AND mpit.tag_quantity IS NULL
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)
AND msn.inventory_item_id = msik.inventory_item_id
AND msn.current_organization_id = msik.organization_id
AND wlpn.lpn_id(+) = msn.lpn_id
-- Bug# 2770853
-- Honor the serial material status for physical inventory adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
8,
NULL,
'Y',
p_organization_id,
msn.inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT NVL(serial_discrepancy_option, 2),
NVL(container_discrepancy_option, 2),
NVL(orientation_code, 1)
INTO l_serial_discrepancy_option, l_container_discrepancy_option,
l_orientation_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((current_organization_id = p_organization_id
AND current_status IN (1, 3, 4, 6)
)
OR current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
-- Bug# 2591158
-- Only allow serials that are within the scope of the header
-- for unscheduled cycle count entries
-- Bug# 2778771
-- Do this check only if the serial status is 3, resides in stores
AND (l_orientation_code = 1 OR
(msn.current_status = 3
AND msn.current_subinventory_code IN
(SELECT subinventory
FROM mtl_cc_subinventories
WHERE cycle_count_header_id = p_cycle_count_header_id))
OR msn.current_status <> 3
)
AND serial_number LIKE (p_serial_number)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
OR current_status IN (1, 6)
) --newly generated
-- Do not include the serial numbers which are pending approval
-- for the same cycle count header
AND msn.serial_number NOT IN
(SELECT mcce.serial_number
FROM mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code = 2
AND NVL(mcce.export_flag, 2) = 2)
AND msn.serial_number NOT IN
(SELECT mcsn.serial_number
FROM mtl_cc_serial_numbers mcsn, mtl_cycle_count_entries mcce
WHERE mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code = 2
AND NVL(mcce.export_flag, 2) = 2)
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY 1 ASC;
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_inventory_item_id
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number LIKE (p_serial_number)
AND msn.serial_number = mcce.serial_number
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
mtl_material_statuses_tl mms, mtl_cycle_count_entries mcce
WHERE msn.inventory_item_id = p_inventory_item_id
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
AND msn.serial_number LIKE (p_serial_number)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT NVL(serial_discrepancy_option, 2),
NVL(container_discrepancy_option, 2),
NVL(orientation_code, 1)
INTO l_serial_discrepancy_option, l_container_discrepancy_option,
l_orientation_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_inventory_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((current_organization_id = p_organization_id
AND current_status IN (1, 3, 4, 6)
)
OR current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
-- Bug# 2591158
-- Only allow serials that are within the scope of the header
-- for unscheduled cycle count entries
-- Bug# 2778771
-- Do this check only if the serial status is 3, resides in stores
AND (l_orientation_code = 1 OR
(msn.current_status = 3
AND msn.current_subinventory_code IN
(SELECT subinventory
FROM mtl_cc_subinventories
WHERE cycle_count_header_id = p_cycle_count_header_id))
OR msn.current_status <> 3
)
AND (NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
OR current_status IN (1, 6)
) --newly generated
AND serial_number LIKE (p_to_serial_number)
AND serial_number LIKE (l_prefix || '%')
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number > p_from_serial_number
-- Do not include the serial numbers which are pending approval
-- for the same cycle count header
AND msn.serial_number NOT IN
(SELECT mcce.serial_number
FROM mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code = 2
AND NVL(mcce.export_flag, 2) = 2)
AND msn.serial_number NOT IN
(SELECT mcsn.serial_number
FROM mtl_cc_serial_numbers mcsn, mtl_cycle_count_entries mcce
WHERE mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code = 2
AND NVL(mcce.export_flag, 2) = 2)
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY 1 ASC;
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_inventory_item_id
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number LIKE (p_to_serial_number)
AND msn.serial_number LIKE (l_prefix || '%')
AND msn.serial_number = mcce.serial_number
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.serial_number > p_from_serial_number
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
mtl_material_statuses_tl mms, mtl_cc_serial_numbers mcsn
WHERE msn.inventory_item_id = p_inventory_item_id
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (3, 4)
)
OR msn.current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
OR (p_parent_lpn_id IS NOT NULL
AND l_container_discrepancy_option = 1
)
)
AND NVL(msn.lot_number, '###') = NVL(p_lot_number, '###')
AND msn.serial_number LIKE (p_to_serial_number)
AND msn.serial_number LIKE (l_prefix || '%')
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.serial_number > p_from_serial_number
AND msn.serial_number = mcsn.serial_number
AND mcsn.cycle_count_entry_id = mcce.cycle_count_entry_id
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND NVL(mcce.parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
p_inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT NVL(serial_discrepancy_option, 2), NVL(orientation_code, 1)
INTO l_serial_discrepancy_option, l_orientation_code
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_cycle_count_header_id;
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.inventory_item_id
, msik.concatenated_segments
, msn.revision
, msn.lot_number
, msn.lpn_id
, wlpn.license_plate_number
, msn.current_status
, msik.primary_uom_code
FROM mtl_serial_numbers msn, mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
WHERE (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND ((msn.current_organization_id = p_organization_id
AND msn.current_status IN (1, 3, 4, 6)
)
OR msn.current_status = 5
)
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
)
-- Bug# 2591158
-- Only allow serials that are within the scope of the header
-- for unscheduled cycle count entries
-- Bug# 2778771
-- Do this check only if the serial status is 3, resides in stores
AND (l_orientation_code = 1 OR
(msn.current_status = 3
AND msn.current_subinventory_code IN
(SELECT subinventory
FROM mtl_cc_subinventories
WHERE cycle_count_header_id = p_cycle_count_header_id))
OR msn.current_status <> 3
)
AND msn.serial_number LIKE (p_serial_number)
AND msn.inventory_item_id = msik.inventory_item_id
AND msn.current_organization_id = msik.organization_id
AND wlpn.lpn_id(+) = msn.lpn_id
-- Do not include the serial numbers which are pending approval
-- for the same cycle count header
AND msn.serial_number NOT IN (SELECT mcce.serial_number
FROM mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.organization_id = p_organization_id
AND mcce.inventory_item_id = msn.inventory_item_id
AND mcce.entry_status_code = 2
AND NVL(mcce.export_flag, 2) = 2)
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
msn.inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT UNIQUE msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.inventory_item_id
, msik.concatenated_segments
, msn.revision
, msn.lot_number
, msn.lpn_id
, wlpn.license_plate_number
, msn.current_status
, msik.primary_uom_code
FROM mtl_serial_numbers msn, mtl_cycle_count_entries mcce,
mtl_system_items_kfv msik, wms_license_plate_numbers wlpn
WHERE (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
)
AND msn.current_organization_id = p_organization_id
AND ((msn.current_subinventory_code = p_subinventory_code
AND NVL(msn.current_locator_id, -99999) = NVL(p_locator_id, -99999)
)
OR l_serial_discrepancy_option = 1
)
AND msn.serial_number LIKE (p_serial_number)
AND msn.serial_number = mcce.serial_number
AND msn.inventory_item_id = mcce.inventory_item_id
AND NVL(msn.lpn_id, -99999) = NVL(mcce.parent_lpn_id, -99999)
AND mcce.cycle_count_header_id = p_cycle_count_header_id
AND mcce.organization_id = p_organization_id
AND mcce.entry_status_code IN (1, 3)
AND NVL(mcce.export_flag, 2) = 2
AND msn.inventory_item_id = msik.inventory_item_id
AND msn.current_organization_id = msik.organization_id
AND wlpn.lpn_id(+) = msn.lpn_id
-- Bug# 2770853
-- Honor the serial material status for cycle count adjustments
AND INV_MATERIAL_STATUS_GRP.is_status_applicable(NULL,
NULL,
4,
NULL,
'Y',
p_organization_id,
msn.inventory_item_id,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND current_status IN (1, 3, 5)
AND current_status IN (1, 3, 5, 7)
AND (p_from_lot_number IS NULL
OR lot_number >= p_from_lot_number
)
AND (p_to_lot_number IS NULL
OR lot_number <= p_to_lot_number
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial_number);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND current_status IN (1, 3, 5)
AND current_status IN (1, 3, 5, 7)
AND (p_from_lot_number IS NULL
OR lot_number >= p_from_lot_number
)
AND (p_to_lot_number IS NULL
OR lot_number <= p_to_lot_number
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number >= p_from_serial_number
AND serial_number LIKE (p_serial_number);
SELECT serial_number
, 0
, 0
, 0
, 0
, ''
, ''
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_item_id
AND NVL(lot_number, 'NOLOT') = NVL(p_lot, 'NOLOT')
AND serial_number LIKE (p_serial)
AND group_mark_id IS NULL
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.lpn_id = p_lpn_id
AND msn.current_organization_id = p_organization_id
--AND msn.current_status = 5 /* Intransit */
AND msn.current_status IN (5, 7) /* Intransit, Resides in Receiving */
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.inspection_status = 1 /* yet to be inspected */
AND msn.serial_number LIKE (p_serial)
AND Nvl(msn.lot_number,'@@@') = Nvl(p_lot_number,Nvl(msn.lot_number,'@@@'))
AND Nvl(msn.group_mark_id,-1) <> 2
ORDER BY LPAD(serial_number, 20);
SELECT serial_number_control_code
INTO l_serial_number_control_code
FROM mtl_system_items_b
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, ' '
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 1
AND serial_number LIKE (p_serial_number)
ORDER BY LPAD(serial_number, 20);
select serial_number,current_subinventory_code,current_locator_id,lot_number,0,0,''
from mtl_serial_numbers msn
where inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id
and (group_mark_id is null or group_mark_id = -1 )
and nvl(current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
and nvl(current_locator_id,0) = nvl(p_locator_id,0)
and current_status = 3
and (lpn_id is NULL OR lpn_id = 0)
and wip_entity_id is NULL
and msn.serial_number like (p_serial_number || '%')
order by lpad(msn.serial_number,20);
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, ''
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_id
AND msn.lpn_id = p_lpn_id
AND NVL(line_mark_id, -999) <> 1
AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
AND msn.serial_number LIKE p_serial
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_unpack, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y'
AND NOT EXISTS (select 1
from mtl_reservations mr
where mr.reservation_id = msn.reservation_id
and mr.lpn_id = p_lpn_id)
ORDER BY LPAD(msn.serial_number, 20);
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, ''
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_id
AND msn.lpn_id = p_lpn_id
AND NVL(line_mark_id, -9) <> 1
AND NVL(lot_number, '@') = NVL(NVL(p_lot_number, '@'), NVL(lot_number, '@'))
AND NVL(revision, '@') = NVL(NVL(p_revision, '@'), NVL(revision, '@'))
AND msn.serial_number LIKE (p_serial)
AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_split, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y')
AND NOT EXISTS (select 1
from mtl_reservations mr
where mr.reservation_id = msn.reservation_id
and mr.lpn_id = p_lpn_id)
ORDER BY LPAD(msn.serial_number, 20);
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, ''
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_id
AND msn.lpn_id = p_lpn_id
AND NVL(line_mark_id, -9) <> 1
AND NVL(lot_number, '@') = NVL(NVL(p_lot_number, '@'), NVL(lot_number, '@'))
AND NVL(revision, '@') = NVL(NVL(p_revision, '@'), NVL(revision, '@'))
AND msn.serial_number LIKE (p_serial)
AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_split, NULL, NULL, p_organization_id, msn.inventory_item_id, NULL, NULL, NULL, msn.serial_number, 'S') = 'Y')
ORDER BY LPAD(msn.serial_number, 20);
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
FROM mtl_serial_numbers msn
WHERE msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_item_id
AND msn.lpn_id = p_lpn_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
AND msn.serial_number LIKE (p_serial)
AND current_status = 3
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, NULL, NULL, p_organization_id, NULL, NULL, NULL, NULL, p_serial, 'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 'NULL'
, current_status
, 'NULL'
FROM mtl_serial_numbers
WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
AND current_organization_id = p_organization_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((NVL(current_subinventory_code, '@@@') = NVL(NVL(p_subinventory_code, '@@@'), NVL(current_subinventory_code, '@@@'))
AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
AND current_status = 3
)
OR current_status = 1
OR current_status = 6
)
AND serial_number LIKE (p_serial_number)
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 'NULL'
, current_status
, 'NULL'
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND NVL(line_mark_id, -999) <> 1
AND current_subinventory_code = p_subinventory_code
AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
AND current_status = 3
AND lpn_id IS NULL
AND serial_number LIKE p_serial_number
AND inv_material_status_grp.is_status_applicable
(
'TRUE', NULL,
inv_globals.g_type_container_pack,
NULL, NULL,
p_organization_id, inventory_item_id,
NULL, NULL, NULL, serial_number, 'S') = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 'NULL'
, current_status
, 'NULL'
FROM mtl_serial_numbers
WHERE inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
AND current_organization_id = p_organization_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((NVL(current_subinventory_code, '@@@') = NVL(NVL(p_subinventory_code, '@@@'), NVL(current_subinventory_code, '@@@'))
AND NVL(current_locator_id, -1) = NVL(NVL(TO_NUMBER(p_locator_id), -1), NVL(current_locator_id, -1))
AND NVL(lot_number, '@@@') = NVL(NVL(p_lot_number, '@@@'), NVL(lot_number, '@@@'))
AND NVL(revision, '@@@') = NVL(NVL(p_revision, '@@@'), NVL(revision, '@@@'))
AND current_status = 3
)
)
AND serial_number LIKE (l_prefix || '%')
AND lpn_id IS NULL
AND serial_number >= NVL(p_from_serial_number, serial_number)
AND serial_number LIKE (p_serial_number)
AND (inv_material_status_grp.is_status_applicable('TRUE', NULL, inv_globals.g_type_container_pack, NULL, NULL, p_organization_id, inventory_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y')
ORDER BY LPAD(serial_number, 20);
PROCEDURE get_cgupdate_serial_lov(
x_serial OUT NOCOPY t_genref
, p_organization_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_lpn_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_subinventory_code IN VARCHAR2
, p_locator_id IN NUMBER
, p_revision IN VARCHAR2
, p_cost_group_id IN NUMBER
) IS
BEGIN
IF p_lpn_id IS NULL THEN
OPEN x_serial FOR
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, ''
, msn.current_status
, mms.status_code
, mil.concatenated_segments
, msn.revision
, msn.cost_group_id
, ccg.cost_group
FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
WHERE (group_mark_id IS NULL
OR group_mark_id = -1
)
AND mms.status_id(+) = msn.status_id
AND mms.language (+) = userenv('LANG')
AND ccg.cost_group_id = msn.cost_group_id
AND msn.current_locator_id = mil.inventory_location_id
AND mil.organization_id = p_organization_id
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
AND msn.current_status = 3
AND (msn.group_mark_id IS NULL
OR (msn.group_mark_id <> 1)
)
AND (p_revision IS NULL
OR (msn.revision = p_revision)
)
AND msn.cost_group_id = NVL(p_cost_group_id, msn.cost_group_id)
AND msn.current_locator_id = NVL(p_locator_id, msn.current_locator_id)
AND msn.current_subinventory_code = NVL(p_subinventory_code, msn.current_subinventory_code)
AND msn.serial_number LIKE (p_serial_number)
AND msn.lpn_id IS NULL
AND msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
ORDER BY serial_number;
SELECT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, ''
, msn.current_status
, mms.status_code
, mil.concatenated_segments
, msn.revision
, msn.cost_group_id
, ccg.cost_group
FROM mtl_item_locations_kfv mil, mtl_serial_numbers msn, cst_cost_groups ccg, mtl_material_statuses_tl mms
WHERE (group_mark_id IS NULL
OR group_mark_id = -1
)
AND mms.status_id(+) = msn.status_id
AND mms.language (+) = userenv('LANG')
AND ccg.cost_group_id = msn.cost_group_id
AND msn.current_locator_id = mil.inventory_location_id
AND mil.organization_id = p_organization_id
AND inv_material_status_grp.is_status_applicable('TRUE', NULL, 86, NULL, NULL, p_organization_id, p_inventory_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
AND msn.current_status = 3
AND (msn.group_mark_id IS NULL
OR (msn.group_mark_id <> 1)
)
AND (p_revision IS NULL
OR (msn.revision = p_revision)
)
AND msn.cost_group_id = NVL(p_cost_group_id, msn.cost_group_id)
AND msn.current_locator_id = NVL(p_locator_id, msn.current_locator_id)
AND msn.current_subinventory_code = NVL(p_subinventory_code, msn.current_subinventory_code)
AND msn.serial_number LIKE (p_serial_number)
AND msn.lpn_id = p_lpn_id
AND msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
ORDER BY serial_number;
END get_cgupdate_serial_lov;
SELECT MIN(expiration_date)
INTO x_expiration_date
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
INTO x_expiration_date
FROM DUAL;
SELECT (SYSDATE + NVL(p_shelf_life_days, 0))
INTO x_expiration_date
FROM DUAL;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_organization_id = p_organization_id
AND current_status = 3
AND current_subinventory_code = p_subinv_code
AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND NVL(msn.lot_number, 0) = NVL(p_lot_number, 0) --retrict to lot numbers
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT msnt.fm_serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status = 3
AND msn.current_subinventory_code = p_subinv_code
AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msnt.fm_serial_number, 'S') = 'Y'
AND msn.serial_number = msnt.fm_serial_number
AND msnt.fm_serial_number LIKE (p_serial)
AND msnt.transaction_temp_id = p_transaction_temp_id
ORDER BY LPAD(msnt.fm_serial_number, 20);
SELECT msnt.fm_serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status = 3
AND msn.current_subinventory_code = p_subinv_code
AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msnt.fm_serial_number, 'S') = 'Y'
AND msn.serial_number = msnt.fm_serial_number
AND msnt.fm_serial_number LIKE (p_serial)
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number = p_lot_number
AND mtlt.transaction_temp_id = p_transaction_temp_id
ORDER BY LPAD(msnt.fm_serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_organization_id = p_organization_id
AND current_status = 3
AND current_subinventory_code = p_subinv_code
AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND (p_revision IS NULL
OR (msn.revision = p_revision)
)
AND NVL(msn.lot_number, 0) = NVL(p_lot_number, 0) --retrict to lot numbers
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'S') = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT mag.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status = 3
AND msn.current_subinventory_code = p_subinv_code
--AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, mag.serial_number, 'S') = 'Y'
AND msn.serial_number = mag.serial_number
AND mag.serial_number LIKE (p_serial)
AND (p_revision IS NULL
OR (msn.revision = p_revision)
)
ORDER BY LPAD(mag.serial_number, 20);
SELECT mag.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, msn.lot_number
, 0
, msn.current_status
, mms.status_code
FROM wms_allocations_gtmp mag, mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND msn.current_organization_id = p_organization_id
AND msn.current_status = 3
AND msn.current_subinventory_code = p_subinv_code
--AND NVL(msn.lpn_id, 0) = NVL(p_lpn_id, 0)
AND NVL(msn.current_locator_id, 0) = NVL(DECODE(p_locator_id, -1, msn.current_locator_id, p_locator_id), 0)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(l_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, p_subinv_code, msn.current_locator_id, msn.lot_number, mag.serial_number, 'S') = 'Y'
AND msn.serial_number = mag.serial_number
AND mag.serial_number LIKE (p_serial)
AND mag.lot_number = p_lot_number
AND (p_revision IS NULL
OR (msn.revision = p_revision)
)
ORDER BY LPAD(mag.serial_number, 20);
SELECT DISTINCT serial_number
, 'NULL'
, 0
, 'NULL'
, --lot_number,
'NULL'
, 0
, --current_status,
'NULL'
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
SELECT DISTINCT serial_number
, 'NULL'
, 0
, 'NULL'
, --lot_number,
'NULL'
, 0
, --current_status,
'NULL'
FROM mtl_serial_numbers
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND LENGTH(serial_number) = LENGTH(p_from_serial_number)
AND serial_number LIKE (l_prefix || '%')
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE current_organization_id = p_org_id
AND group_mark_id IS NOT NULL
AND lpn_id = p_lpn_id
AND inventory_item_id = p_item_id
AND ((revision = p_revision
AND p_revision IS NOT NULL
)
OR (revision IS NULL
AND p_revision IS NULL
)
)
AND last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING');
SELECT DISTINCT serial_number
, 'NULL'
, 0
, NVL(lot_number, '')
, --lot_number,
'NULL'
, 0
, --current_status,
'NULL'
FROM mtl_serial_numbers
WHERE current_organization_id = p_org_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND lpn_id = p_lpn_id
AND inventory_item_id = p_item_id
AND ((revision = p_revision
AND p_revision IS NOT NULL
)
OR (revision IS NULL
AND p_revision IS NULL
)
)
AND last_txn_source_name IN ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'RETURN TO RECEIVING')
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
SELECT fm_serial_number || '-' || to_serial_number
, 0
, 0
, 0
, 0
, ''
, ''
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id
ORDER BY LPAD(fm_serial_number, 20);
SELECT msnt.fm_serial_number || '-' || msnt.to_serial_number
, 0
, 0
, 0
, 0
, ''
, ''
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
ORDER BY LPAD(fm_serial_number, 20);
SELECT DISTINCT msn.serial_number
, msn.current_subinventory_code
, msn.current_locator_id
, --I Development Bug 2634570
--milv.concatenated_segments,
inv_project.get_locsegs(msn.current_locator_id, p_current_organization_id)
, msn.inventory_item_id
, msiv.concatenated_segments
, msiv.description
, msn.revision
, msn.lot_number
, NVL(msiv.restrict_subinventories_code, 2)
, NVL(msiv.restrict_locators_code, 2)
, msiv.serial_number_control_code
, msi.asset_inventory
, msiv.location_control_code
, msiv.primary_uom_code
, --I Development Bug 2634570
inv_project.get_project_id
, inv_project.get_project_number
, inv_project.get_task_id
, inv_project.get_task_number
FROM mtl_serial_numbers msn
, mtl_system_items_vl msiv
, mtl_item_locations_kfv milv
, mtl_secondary_inventories msi
WHERE msn.current_organization_id = p_current_organization_id
AND msn.lpn_id IS NULL
AND (msn.group_mark_id IS NULL
OR msn.group_mark_id = -1
OR ( msn.group_mark_id IS NOT NULL
-- Performance Bug : 5367744
AND NOT EXISTS (
SELECT 1
FROM mtl_reservations mr
WHERE mr.reservation_id = msn.reservation_id
AND NVL(mr.staged_flag, 'N') = 'Y')
AND NOT EXISTS (
SELECT 1
FROM mtl_serial_numbers_temp msnt
WHERE msn.serial_number BETWEEN msnt.fm_serial_number
AND msnt.to_serial_number)
)
)
AND msn.current_status = 3
AND msn.serial_number LIKE (p_serial_number || '%')
AND milv.organization_id(+) = p_current_organization_id
AND milv.inventory_location_id(+) = msn.current_locator_id
AND msiv.organization_id = p_current_organization_id
AND msiv.inventory_item_id = msn.inventory_item_id
AND msi.organization_id = p_current_organization_id
AND msi.secondary_inventory_name = msn.current_subinventory_code;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
, ''
, msn.revision
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
OR group_mark_id IN (SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = p_move_order_line_id
UNION
SELECT mtlt.serial_transaction_temp_id
FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mtlt.serial_transaction_temp_id IS NOT NULL)
)
AND current_organization_id = p_organization_id
AND current_status = 3
AND msn.lpn_id IS NULL
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, msn.current_subinventory_code, msn.current_locator_id, msn.lot_number, msn.serial_number, 'A') = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, ''
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND (current_status = 1 or current_status = 6
OR (current_status = 4
AND last_txn_source_type_id = 5 -- returned to WIP
AND (NVL(lot_number, '%') LIKE l_lot_number)))
AND serial_number LIKE (p_serial)
AND (group_mark_id IS NULL OR group_mark_id = -1)
AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT DISTINCT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, ''
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND current_status = 3
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
-- bug 2360642: don't select serials that are packed into lpns
AND msn.lpn_id IS NULL
AND NVL(current_subinventory_code, '$@#$%') = NVL(p_subinv, NVL(current_subinventory_code, '$@#$%'))
AND NVL(current_locator_id, -1) = DECODE(p_locator_id, -1, NVL(current_locator_id, -1), p_locator_id)
AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
AND NVL(revision, '$@#$%') = NVL(p_revision, NVL(revision, '$@#$%'))
AND serial_number LIKE (p_serial)
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) =
'Y'
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, ''
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND (current_status = 1 or current_status = 6
OR (current_status = 4
AND last_txn_source_type_id = 5 -- returned to WIP
AND (((p_wip_entity_id <> -1)
AND (p_wip_entity_id = last_txn_source_id)
AND (NVL(lot_number, '%') LIKE l_lot_number)
)
OR ((p_wip_entity_id = -1)
AND (NVL(lot_number, '%') LIKE l_lot_number)
AND (4 = (SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = last_txn_source_id)
)
)
)
)
)
AND serial_number LIKE (p_serial)
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND (inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_transaction_type_id, NULL, NULL, current_organization_id, inventory_item_id, current_subinventory_code, current_locator_id, lot_number, serial_number, 'S')) = 'Y'
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND last_txn_source_type_id = 5 -- issued to WIP
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND (((p_wip_entity_id <> -1)
AND (p_wip_entity_id = last_txn_source_id)
)
OR ((p_wip_entity_id = -1)
AND (4 = (SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = last_txn_source_id)
)
)
)
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND last_txn_source_type_id = 5 -- issued to WIP
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND NVL(lot_number, '$@#$%') = NVL(p_lot, NVL(lot_number, '$@#$%'))
AND nvl(msn.revision, '$@#$%') = nvl(p_revision, '$@#$%')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL,
p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND (((p_wip_entity_id <> -1)
AND (p_wip_entity_id = last_txn_source_id)
)
OR ((p_wip_entity_id = -1)
AND (4 = (SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = last_txn_source_id)
)
)
)
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
select primary_item_id
into l_wip_assembly_id
From wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
And organization_id = p_organization_id;
select serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
from mtl_serial_numbers msn, mtl_material_statuses_tl mms
where inventory_item_id = l_wip_assembly_id
and msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND CURRENT_organization_id = p_organization_id
and (
((current_status = 1 or current_status = 6 )
AND p_transaction_action_id =1
AND (wip_entity_id = p_wip_entity_id OR wip_entity_id is null)
)
or ((current_status = 3 OR current_status = 4)
AND last_txn_source_type_id =5
AND last_txn_source_id = p_wip_entity_id
AND p_transaction_type_id = 35
)
)--changed for bug 2767928
and inv_material_status_grp.is_status_applicable(
p_wms_installed, p_transaction_type_id,NULL,NULL,
p_organization_id, p_item_id, NULL, NULL, NULL,
serial_number,'S') = 'Y'
and serial_number like (p_serial)
order by lpad(serial_number,20);
SELECT vendor_id
, grade_code
, TO_CHAR(origination_date, 'YYYY/MM/DD HH24:MI:SS')
, date_code
, status_id
, TO_CHAR(change_date, 'YYYY/MM/DD HH24:MI:SS')
, age
, TO_CHAR(retest_date, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(maturity_date, 'YYYY/MM/DD HH24:MI:SS')
, lot_attribute_category
, item_size
, color
, volume
, volume_uom
, place_of_origin
, TO_CHAR(best_by_date, 'YYYY/MM/DD HH24:MI:SS')
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, TO_CHAR(d_attribute1, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute2, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute3, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute4, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute5, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute6, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute7, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute8, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute9, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute10, 'YYYY/MM/DD HH24:MI:SS')
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, supplier_lot_number
, territory_code
, vendor_name
, description
INTO x_vendor_id
, x_grade_code
, x_origination_date
, x_date_code
, x_status_id
, x_change_date
, x_age
, x_retest_date
, x_maturity_date
, x_lot_attribute_category
, x_item_size
, x_color
, x_volume
, x_volume_uom
, x_place_of_origin
, x_best_by_date
, x_length
, x_length_uom
, x_recycled_content
, x_thickness
, x_thickness_uom
, x_width
, x_width_uom
, x_curl_wrinkle_fold
, x_c_attribute1
, x_c_attribute2
, x_c_attribute3
, x_c_attribute4
, x_c_attribute5
, x_c_attribute6
, x_c_attribute7
, x_c_attribute8
, x_c_attribute9
, x_c_attribute10
, x_c_attribute11
, x_c_attribute12
, x_c_attribute13
, x_c_attribute14
, x_c_attribute15
, x_c_attribute16
, x_c_attribute17
, x_c_attribute18
, x_c_attribute19
, x_c_attribute20
, x_d_attribute1
, x_d_attribute2
, x_d_attribute3
, x_d_attribute4
, x_d_attribute5
, x_d_attribute6
, x_d_attribute7
, x_d_attribute8
, x_d_attribute9
, x_d_attribute10
, x_n_attribute1
, x_n_attribute2
, x_n_attribute3
, x_n_attribute4
, x_n_attribute5
, x_n_attribute6
, x_n_attribute7
, x_n_attribute8
, x_n_attribute9
, x_n_attribute10
, x_supplier_lot_number
, x_territory_code
, x_vendor_name
, x_description
FROM mtl_lot_numbers
WHERE organization_id = p_org_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, group_mark_id
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, TO_CHAR(d_attribute1, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute2, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute3, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute4, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute5, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute6, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute7, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute8, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute9, 'YYYY/MM/DD HH24:MI:SS')
, TO_CHAR(d_attribute10, 'YYYY/MM/DD HH24:MI:SS')
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
INTO x_attribute_category
, x_attribute1
, x_attribute2
, x_attribute3
, x_attribute4
, x_attribute5
, x_attribute6
, x_attribute7
, x_attribute8
, x_attribute9
, x_attribute10
, x_attribute11
, x_attribute12
, x_attribute13
, x_attribute14
, x_attribute15
, x_group_mark_id
, x_serial_attribute_category
, x_c_attribute1
, x_c_attribute2
, x_c_attribute3
, x_c_attribute4
, x_c_attribute5
, x_c_attribute6
, x_c_attribute7
, x_c_attribute8
, x_c_attribute9
, x_c_attribute10
, x_c_attribute11
, x_c_attribute12
, x_c_attribute13
, x_c_attribute14
, x_c_attribute15
, x_c_attribute16
, x_c_attribute17
, x_c_attribute18
, x_c_attribute19
, x_c_attribute20
, x_d_attribute1
, x_d_attribute2
, x_d_attribute3
, x_d_attribute4
, x_d_attribute5
, x_d_attribute6
, x_d_attribute7
, x_d_attribute8
, x_d_attribute9
, x_d_attribute10
, x_n_attribute1
, x_n_attribute2
, x_n_attribute3
, x_n_attribute4
, x_n_attribute5
, x_n_attribute6
, x_n_attribute7
, x_n_attribute8
, x_n_attribute9
, x_n_attribute10
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE lpn_id = p_lpn_id
AND current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND NVL(lot_number, 'NOLOT') = NVL(p_lot_number, 'NOLOT')
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial_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,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE lpn_id = p_lpn_id
AND current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial_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,
NULL,
NULL,
NULL,
msn.serial_number,
'S') = 'Y'
ORDER BY LPAD(msn.serial_number, 20);
SELECT serial_number
, 0
, 0
, 0
, 0
, ''
, ''
FROM mtl_serial_numbers msn
, rcv_serials_supply rss
, rcv_supply rs
WHERE msn.lpn_id = p_lpn_id
AND msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND NVL(msn.lot_number, '&*^') = NVL(p_lot_number, '&*^')
AND msn.serial_number LIKE (p_serial)
AND msn.current_status = 7
AND (group_mark_id IS NULL or group_mark_id = -1)
AND rss.serial_num = msn.serial_number
AND rs.lpn_id = p_lpn_id
AND rss.transaction_id = rs.rcv_transaction_id
AND rs.supply_type_code = 'RECEIVING'
ORDER BY LPAD(serial_number, 20);
/* Bug 4574714-Added the procedure to insert into temp table
based on the ENFORCE_RMA_SERIAL_NUM value in
rcv_parameters. This is called before firing
the LOV query for serials for RMA*/
PROCEDURE insert_temp_table_for_serials
(p_organization_id IN NUMBER,
p_item_id IN NUMBER,
p_wms_installed IN VARCHAR2,
p_oe_order_header_id IN NUMBER,
x_returnSerialVal OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
into l_enforce_rma_sn
from RCV_PARAMETERS
where organization_id = p_organization_id;
For c_rma_line in ( select line_id
FROM
OE_ORDER_LINES_all OEL,
OE_ORDER_HEADERS_all OEH
WHERE OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.INVENTORY_ITEM_ID = p_item_id
AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
AND OEL.HEADER_ID = OEH.HEADER_ID
AND OEH.HEADER_ID = p_oe_order_header_id
AND OEL.ORDERED_QUANTITY > NVL(OEL.SHIPPED_QUANTITY,0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
)
Loop
INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
p_api_version => 0.9
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_errorcode => l_errorcode
, p_rma_line_id => c_rma_line.LINE_ID
, p_org_id => P_ORGANIZATION_ID
, p_item_id => p_item_id
);
SELECT count(line_id)
INTO l_count_rows
FROM mtl_rma_serial_temp msrt
WHERE msrt.organization_id = p_organization_id
AND msrt.inventory_item_id = p_item_id ;
END insert_temp_table_for_serials;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE msn.inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND msn.serial_number LIKE (p_serial)
AND EXISTS ( select 'x' from mtl_rma_serial_temp msrt
where msrt.organization_id = p_organization_id
and msrt.inventory_item_id = p_item_id
and msrt.serial_number = msn.serial_number
and msrt.serial_number LIKE (p_serial)
)
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND current_status = 4
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
AND serial_number LIKE (p_serial)
ORDER BY LPAD(serial_number, 20);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, status_code
FROM
(SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE inventory_item_id = p_item_id
AND (group_mark_id IS NULL
OR group_mark_id = -1
)
AND ((current_organization_id = p_organization_id
AND current_status = 1
)
OR (current_status = 4 AND
Nvl(to_number(fnd_profile.value('INV_RESTRICT_RCPT_SER')), 2) = 2)
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y'
UNION
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM rcv_serials_supply rss
, rcv_shipment_lines rsl
, mtl_serial_numbers msn
, mtl_material_statuses_tl mms
WHERE rss.shipment_line_id(+) = rsl.shipment_line_id
AND nvl(rss.supply_type_code, 'SHIPMENT') = 'SHIPMENT'
AND (msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
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 msn.inventory_item_id = p_item_id
AND msn.serial_number = rss.serial_num
AND msn.current_status = 5
AND Nvl(msn.lpn_id,NVL(p_from_lpn_id,-1)) = NVL(p_from_lpn_id, NVL(msn.lpn_id, -1))
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial)
AND inv_material_status_grp.is_status_applicable(p_wms_installed, p_transaction_type_id, NULL, NULL, p_organization_id, p_item_id, NULL, NULL, NULL, serial_number, 'S') = 'Y')
ORDER BY LPAD(serial_number, 20) ;
/* Bug 5577789-Added the procedure to insert into temp table
based on the ENFORCE_RMA_SERIAL_NUM value in
rcv_parameters. This is called before firing
the LOV query for serials for RMA. This is for the deliver step*/
PROCEDURE insert_RMA_serials_for_deliver
(p_organization_id IN NUMBER,
p_item_id IN NUMBER,
p_wms_installed IN VARCHAR2,
p_oe_order_header_id IN NUMBER,
x_returnSerialVal OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
select nvl(ENFORCE_RMA_SERIAL_NUM,'N')
into l_enforce_rma_sn
from RCV_PARAMETERS
where organization_id = p_organization_id;
For c_rma_line in ( select line_id
FROM
OE_ORDER_LINES_all OEL,
OE_ORDER_HEADERS_all OEH
WHERE OEL.LINE_CATEGORY_CODE='RETURN'
AND OEL.INVENTORY_ITEM_ID = p_item_id
AND nvl(OEL.SHIP_FROM_ORG_ID, OEH.SHIP_FROM_ORG_ID) = p_organization_id
AND OEL.HEADER_ID = OEH.HEADER_ID
AND OEH.HEADER_ID = p_oe_order_header_id
AND OEL.ORDERED_QUANTITY >= NVL(OEL.SHIPPED_QUANTITY,0)
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN_DISPOSITION'
)
Loop
INV_RMA_SERIAL_PVT.POPULATE_TEMP_TABLE(
p_api_version => 0.9
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_errorcode => l_errorcode
, p_rma_line_id => c_rma_line.LINE_ID
, p_org_id => P_ORGANIZATION_ID
, p_item_id => p_item_id
);
SELECT count(line_id)
INTO l_count_rows
FROM mtl_rma_serial_temp msrt
WHERE msrt.organization_id = p_organization_id
AND msrt.inventory_item_id = p_item_id ;
END insert_RMA_serials_for_deliver;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND current_status IN (1, 3, 5)
AND current_status IN (1, 3, 5, 7)
AND (p_from_lot_number IS NULL
OR lot_number >= p_from_lot_number
)
AND (p_to_lot_number IS NULL
OR lot_number <= p_to_lot_number
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.lpn_id is null
AND serial_number >= p_from_serial_number
AND serial_number LIKE (p_serial_number);
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND current_status IN (1, 3, 5)
AND current_status IN (1, 3, 5, 7)
AND (p_from_lot_number IS NULL
OR lot_number >= p_from_lot_number
)
AND (p_to_lot_number IS NULL
OR lot_number <= p_to_lot_number
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND serial_number LIKE (p_serial_number)
AND msn.lpn_id is NULL;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND (p_lot_number IS NULL
OR lot_number = p_lot_number)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.lpn_id = p_lpn_id
AND serial_number >= p_from_serial_number
AND serial_number LIKE (p_serial_number)
;
SELECT serial_number
, current_subinventory_code
, current_locator_id
, lot_number
, 0
, current_status
, mms.status_code
FROM mtl_serial_numbers msn, mtl_material_statuses_tl mms
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
--AND current_status IN (1, 3, 5)
AND current_status IN (1, 3, 5, 7)
AND (p_lot_number IS NULL
OR lot_number = p_lot_number
)
AND msn.status_id = mms.status_id(+)
AND mms.language (+) = userenv('LANG')
AND msn.lpn_id = p_lpn_id
AND serial_number LIKE (p_serial_number)
;