How to avoid duplicate record in Oracle Applications Custom forms

It is a very common requirement that end user should not be able to enter same record multiple times.

Solution

Create a function under Program Units in Oracle Forms Builder

FUNCTION DUPLICATE_ENTRY_CHECK (BLK_COL_NAME VARCHAR2)

   RETURN BOOLEAN

IS

   IS_DUPLICATE     BOOLEAN       := FALSE;

   CURRENT_REC_NO   NUMBER (3)    := NULL;

   LAST_REC_NO      NUMBER (3)    := NULL;

   CUR_VAL          VARCHAR2 (20) := NULL;

   FORM_NAME        VARCHAR2 (40) := NULL;

BEGIN

   CURRENT_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

   CUR_VAL := NAME_IN (BLK_COL_NAME);

   FORM_NAME := GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME);

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_FALSE);

   LAST_RECORD;

   IF NAME_IN ('SYSTEM.CURSOR_RECORD') <> '1'

   THEN

      LAST_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

      FIRST_RECORD;

      FOR ROW_COUNT IN 1 .. LAST_REC_NO

      LOOP

         IF :SYSTEM.CURSOR_RECORD <> CURRENT_REC_NO

         THEN

            IF CUR_VAL = NAME_IN (BLK_COL_NAME)

            THEN

               IS_DUPLICATE := TRUE;

               EXIT;

            END IF;

         END IF;

         IF LAST_REC_NO <> ROW_COUNT

         THEN

            NEXT_RECORD;

         END IF;

      END LOOP;

   END IF;

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_TRUE);

   IF IS_DUPLICATE = TRUE

   THEN

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN TRUE;

   ELSE

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN FALSE;

   END IF;

END;

Go to relevant item and create a trigger "KEY-NEXT-ITEM"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF :XX_BB_LINES.SUPPLIER_ID IS NOT NULL

   THEN

      IF NOT DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

      THEN

         --:PO_DETAIL.SALES_TAX:=5;

         NEXT_ITEM;

      ELSE

         -- PRU_MESSAGE(1,'DUPLICATE ENTRY IS NOT ALLOWED.');

         AL_ID := FIND_ALERT ('SUPPLIER');

         AL_BUTTON := SHOW_ALERT (AL_ID);

         RAISE FORM_TRIGGER_FAILURE;

      END IF;

   END IF;

END;

Now go to relevant block and also create a trigger named "KEY-COMMIT"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

   THEN

      AL_ID := FIND_ALERT ('SUPPLIER');

      AL_BUTTON := SHOW_ALERT (AL_ID);

      RAISE FORM_TRIGGER_FAILURE;

   ELSE

      COMMIT_FORM;

   END IF;

END;