Hello, I'm writing a when-button-pressed trigger on a save button for an Oracle Forms 6i form, and it has to fulfill a couple of scenarios.

Here's some background information: the fields we're primarily concerned with are:

n_number,
alert_id,
end_date

For all three scenarios we are comparing candidate records against the following records in the database (for the sake of argument, let's assume they're the only records in the database so far):

alert_id|| n_number|| end_date
-------------------------------------
       1||        5||      _______

       2||        6||    10/25/2009

Scenario 1: The user enters a new record:

alert_id 1
n_number 5
end_date NULL

Objective: prevent the user from committing duplicate rows

Scenario 2: The user enters a new record:

alert_id 1
n_number 10
end_date NULL

Objective: Notify the user that this alert_id already exists, but allow the user the ability to commit the row, if desired.

Scenario 3: The user enters a new record:

alert_id 2
n_number 6
end_date NULL

Objective: Notify the user that this alert_id has occurred in the past (i.e. it has a not-null end_date), but allow the user to commit the row, if desired.

I've written the code, which seems to comply with the first two scenarios, but prevents me from fulfilling the third.

Issues: When I enter the third scenario case, I am prompted to commit the record, but when I attempt this, the "duplicate_stop" alert pops up, preventing me.

Issues: I'm getting the following error: ORA-01843: not a valid month. While testing the code for the third scenario in Toad (hard-coding the values, etc) things seemed to be fine. Why would I encounter these problems at run-time?

Help is very much appreciated.

Thank you