Oracle Forms

Hi folks,

I am new to Forms and to this Fourms. I have a issue with duplicate record while end_dating I have give a cursor and calling in when_validate_records, I am able to find the duplicate record but having problem with end_date. If user end_date a record then they can able to insert the same type of record with new start date not within those period of start date and end date of old record. Any help will be very helpful!

PROCEDURE process_event(event VARCHAR2)
IS
v_record_found VARCHAR2(1);

CURSOR ck_dup_cur(p_accounting_event IN VARCHAR2,
p_accounting_line_type IN VARCHAR2,
p_code IN VARCHAR2,
p_effective_date IN DATE
) Is
SELECT 'Y'
from x_accts_map_t
WHERE accounting_event = p_accounting_event
AND accounting_line_type = p_accounting_line_type
AND ((a_code is NULL))
OR (a_code is not NULL And
a_code = p_code))
AND ((end_date is NULL)
OR (end_date is NOT NULL
AND end_date > p_effective_date));

IF EVENT = 'WHEN-VALIDATE-RECORD' THEN
BEGIN
IF :system.record_status in ('NEW','INSERT') THEN

OPEN ck_dup_cur(
:x_accts_map_t.accounting_event,
:x_accts_map_t.accounting_line_type,
:x_accts_map_t.a_code,
:x_accts_map_t.effective_date);

FETCH ck_dup_cur INTO v_record_found;

IF ck_dup_cur%FOUND THEN

IF v_record_found = 'Y' then

v_record_found:= validate_insert_date(p_effective_date,
p_end_date,
p_error_code,
p_error_message);

if p_error_code <> 0 and p_error_code <> 100 then
fnd_message.set_name('XX','XX_ORACLE_ERROR');
fnd_message.SET_TOKEN('SQLCODE',SQLCODE);
fnd_message.SET_TOKEN('SQLERRM',SQLERRM);
fnd_message.error;
raise form_trigger_failure;
end if;

if v_record_found = 'Y' then
fnd_message.set_string('Record already exists for the given criteria, please check values');
fnd_message.error;
raise form_trigger_failure;
end if;
END IF;
CLOSE ck_dup_cur;
fnd_message.set_string('Record already exists for the given criteria, please check values');
fnd_message.error;
raise form_trigger_failure;
ELSE
CLOSE ck_dup_cur;
END IF;
END IF;
EXCEPTION
WHEN INVALID_ITEM THEN
FND_MESSAGE.ERROR;
RAISE FORM_TRIGGER_FAILURE;
WHEN OTHERS THEN
MESSAGE(SQLCODE || SUBSTR(SQLERRM,1,80));
RAISE FORM_TRIGGER_FAILURE;
END;

/******************************************************************************/


FUNCTION validate_insert_date(p_effective_date IN VARCHAR2,
p_end_date IN VARCHAR2,
p_error_code OUT number,
p_error_message OUT varchar2)
RETURN varchar2 IS
p_natural x_accts_map_t.%rowtype;
v_error_code number;
v_error_message varchar2(1000);
v_record_found varchar2(1);

BEGIN

begin

v_record_found := NULL;

SELECT 'Y'
INTO v_record_found
from x_accts_map_t.
WHERE accounting_event = p_natural.accounting_event
AND accounting_line_type = p_natural.accounting_line_type
AND ((a_code is NULL))
OR (a_code is not null And a_code = p_natural.a_code))
And ((:x_accts_map_t.effective_date >= p_effective_date and :x_accts_map_t.effective_date <= nvl (p_end_date,:x_accts_map_t.effective_date))
or (:x_accts_map_t.end_date >= p_effective_date and :x_accts_map_t.end_date <= nvl(p_end_date,:x_accts_map_t.end_date)));

exception
when no_data_found then
v_record_found := 'N';
when others then
p_error_code := SQLCODE;
p_error_message := SQLERRM;
v_record_found := 'N';
end;
return v_record_found;
END;