SELECT DISTINCT wlpn.license_plate_number,
wlpn.lpn_id
FROM wms_license_plate_numbers wlpn,
mtl_txn_request_lines mtrl
WHERE wlpn.lpn_context = 3
AND wlpn.lpn_id = mtrl.LPN_ID
AND mtrl.wms_process_flag = 2
AND mtrl.quantity - NVL(mtrl.quantity_delivered,0) > 0
AND mtrl.line_status = 7
AND EXISTS
(SELECT '1'
FROM mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.MOVE_ORDER_TYPE = 6
)
UNION ALL
SELECT DISTINCT wlpn.license_plate_number,
wlpn.lpn_id
FROM wms_license_plate_numbers wlpn,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi
WHERE wlpn.lpn_context = 3
AND wlpn.lpn_id = mtrl.LPN_ID
AND mtrl.wms_process_flag = 1
AND mtrl.quantity - NVL(mtrl.quantity_delivered,0) > 0
AND mtrl.line_status = 7
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = wooi.source_task_id
AND wooi.operation_type_id = 2
AND wooi.operation_status = 2
AND EXISTS
(SELECT '1'
FROM mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.MOVE_ORDER_TYPE = 6
);
wlpn.lpn_id
FROM wms_license_plate_numbers wlpn,
mtl_txn_request_lines mtrl
WHERE wlpn.lpn_context = 3
AND wlpn.lpn_id = mtrl.LPN_ID
AND mtrl.wms_process_flag = 2
AND mtrl.quantity - NVL(mtrl.quantity_delivered,0) > 0
AND mtrl.line_status = 7
AND EXISTS
(SELECT '1'
FROM mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.MOVE_ORDER_TYPE = 6
)
UNION ALL
SELECT DISTINCT wlpn.license_plate_number,
wlpn.lpn_id
FROM wms_license_plate_numbers wlpn,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi
WHERE wlpn.lpn_context = 3
AND wlpn.lpn_id = mtrl.LPN_ID
AND mtrl.wms_process_flag = 1
AND mtrl.quantity - NVL(mtrl.quantity_delivered,0) > 0
AND mtrl.line_status = 7
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = wooi.source_task_id
AND wooi.operation_type_id = 2
AND wooi.operation_status = 2
AND EXISTS
(SELECT '1'
FROM mtl_txn_request_headers mtrh
WHERE mtrl.header_id = mtrh.header_id
AND mtrh.MOVE_ORDER_TYPE = 6
);