Restrict user to enter the duplicate seal number(shipment refnum) for different shipments
I want to add an action check to restrict user to enter the same seal number for different shipment. We are capturing Seal Number in shipment refnum.
It should be unique Seal Number for each shipment.
I have created below saved query but it is not working as expected.
select distinct 1 from shipment_refnum sr where not exists (select 1 from shipment_refnum where
shipment_refnum_value = sr.shipment_refnum_value and shipment_refnum_qual_gid=sr.shipment_refnum_qual_gid
and sr.shipment_refnum_qual_gid='DOMAIN_NAME.SEAL NUMBER' and shipment_gid <> sr.shipment_gid)
and sr.shipment_gid = ? AND SR.SHIPMENT_REFNUM_QUAL_GID='DOMAIN_NAME.SEAL NUMBER'