We commonly face the issue of duplicate suppliers being created. I am attempting to create a custom event to do a soft validation when a supplier is created to check if there are others with a similar name but when I am creating a new supplier I do not get a message when I should. I am able to get a message if I use ERROR_SYS.RECORD_GENERAL instead of Client_SYS.Add_Info. Any suggesetions?
DECLARE
LCOUNT NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT
COUNT(VENDOR_NAME) VN INTO LCOUNT
FROM
(
SELECT NAME,
UTL_MATCH.jaro_winkler_similarity(UPPER('&NEW:NAME'),UPPER(VENDOR_NAME)) UTL
FROM SUPPLIER_INFO_GENERAL
WHERE UTL_MATCH.jaro_winkler_similarity(upper('&NEW:NAME'),upper(VENDOR_NAME)) > 80
ORDER BY 2 DESC
)
WHERE ROWNUM=1
;
IF LCOUNT > 0
THEN
IFSAPP.Client_SYS.Add_Info('Supplier ',':A similar supplier name already exists.');
END IF;
END;