Question

Duplicate Supplier Check

  • 18 September 2023
  • 0 replies
  • 47 views

Userlevel 4
Badge +9
  • Sidekick (Customer)
  • 63 replies

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; 


0 replies

Be the first to reply!

Reply