Avoid duplicate record in form
Create a function under Program Units in Form Builder.
FUNCTION DUPLICATE_ENTRY_CHECK (BLK_COL_NAME VARCHAR2)
RETURN BOOLEAN
IS
IS_DUPLICATE BOOLEAN := FALSE;
CURRENT_REC_NO NUMBER (30) := NULL;
LAST_REC_NO NUMBER (30) := NULL;
CUR_VAL VARCHAR2 (100) := 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;
exception when others
then
null;
END;
Go to relevant item and create a trigger "KEY-NEXT-ITEM"
DECLARE
AL_ID ALERT;
AL_BUTTON NUMBER;
BEGIN
IF :LINES.VAT_REGISTRATION_NO IS NOT NULL
THEN
IF NOT DUPLICATE_ENTRY_CHECK ('LINES.VAT_REGISTRATION_NO')
THEN
NEXT_ITEM;
ELSE
AL_ID := FIND_ALERT ('VAT_REGISTRATION_ALERT');
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"
DECLARE
AL_ID ALERT;
AL_BUTTON NUMBER;
BEGIN
IF DUPLICATE_ENTRY_CHECK ('LINES.VAT_REGISTRATION_NO')
THEN
AL_ID := FIND_ALERT ('VAT_REGISTRATION_ALERT');
AL_BUTTON := SHOW_ALERT (AL_ID);
RAISE FORM_TRIGGER_FAILURE;
ELSE
COMMIT_FORM;
END IF;
No comments:
Post a Comment