Any idea for limiting NetSuite Lot/Serial Number to be unique?

Question

I am facing a problem that NetSuite Serial/ Lot number can be duplicated Like the picture below,

And I wanna prevent duplicated Lot Numbers from occurring by setting/ developing, Or one step back, one report/ SuiteQL to monitor duplicated Lot Numbers.

Thanks in advance for any tips!

Answer 1

I think I had overcome the problem of monitoring the duplicate Lot Number, just like the screenshot below.

Thanks for SuiteQL Query Tool support And you may get the SQL bellow.

--2022/3/4 Query Lot/Serial Number Duplicate Purchase Order
SELECT
    B.tran_id,
    A.inventory_number
FROM
(
SELECT
    inventoryNumber.inventorynumber AS inventory_number
FROM
    transaction INNER JOIN
        transactionLine ON transaction.id = transactionLine.transaction
    INNER JOIN
        inventoryAssignment ON (transactionLine.id = inventoryAssignment.transactionline
        AND
        transactionLine.transaction = inventoryAssignment.transaction )
    INNER JOIN
        inventoryNumber ON inventoryAssignment.inventorynumber = inventoryNumber.id
WHERE
    UPPER(transaction.type) IN ('PURCHORD')
GROUP BY
    inventoryNumber.inventorynumber
HAVING COUNT(inventoryNumber.inventorynumber) > 1
) AS A
JOIN
(
    SELECT
    transaction.tranid AS tran_id,
    inventoryNumber.inventorynumber AS lot_number
FROM
    transaction FULL JOIN
        transactionLine ON transaction.id = transactionLine.transaction
    INNER JOIN
        inventoryAssignment ON (transactionLine.id = inventoryAssignment.transactionline
        AND
        transactionLine.transaction = inventoryAssignment.transaction )
    INNER JOIN
        inventoryNumber ON inventoryAssignment.inventorynumber = inventoryNumber.id
WHERE
    UPPER(transaction.type) IN ('PURCHORD')
) AS B
    ON A.inventory_number = B.lot_number