DECLARE
      al_button    NUMBER;
    al_button1     NUMBER;
    al_button2     NUMBER;
       
    v_n           VARCHAR2(50):= NULL;
    v_end_date    DATE;
    v_alert_id    VARCHAR2(50);

    lv_exists      CHAR(1);
    ln_count       NUMBER := 0;
   
BEGIN

 BEGIN
    SELECT 'X', n_number, alert_id, end_date
    INTO lv_exists, v_n, v_alert_id, v_end_date
    FROM table_x
    WHERE alert_id = :table_x.alert_id
      AND  n_number = :table_x.n_number
      AND (TO_CHAR(end_date, 'MM/DD/YYYY') = :table_x.end_date
       OR  (end_date IS NULL) );

    EXCEPTION
         WHEN NO_DATA_FOUND THEN
                 COMMIT;
         WHEN OTHERS THEN
             NULL;   
    END;
       BEGIN    
         IF lv_exists = 'X'
        AND v_n IS NOT NULL
        AND v_alert_id IS NOT NULL
        AND v_end_date IS  NULL THEN
            set_alert_property('DUPLICATE_STOP', alert_message_text, 'You cannot enter a duplicate ALERT_ID and NUM');
            al_button1 := show_alert('DUPLICATE_STOP');
            RAISE form_trigger_failure;   
       END IF;
   
     EXCEPTION
         WHEN NO_DATA_FOUND THEN
                 COMMIT;
        WHEN OTHERS THEN
           NULL;
     END;
         BEGIN
             SELECT  alert_id, end_date,  COUNT(*)
              INTO  v_alert_id,  v_end_date, ln_count
              FROM table_x
              WHERE alert_id = :table_x.alert_id
              AND (TO_CHAR(end_date, 'MM/DD/YYYY') = :table_x.end_date
              OR (end_date IS NULL) )
              GROUP BY alert_id, end_date
              HAVING COUNT(*) > 1;
         
            IF ln_count > 1
            AND v_alert_id = :table_x.alert_id
            AND v_end_date IS  NULL THEN
                  set_alert_property('DUPLICATE_WARNING', alert_message_text, 'Alert_id already exists. Do you want to enter it anyways?');
                  al_button2 := show_alert('DUPLICATE_WARNING');
                  IF al_button2 = alert_button1 THEN
                                 COMMIT;
                  END IF;
             END IF;
        EXCEPTION
         WHEN NO_DATA_FOUND THEN
          COMMIT;
         WHEN OTHERS THEN
           NULL;
       END;
            BEGIN
                 SELECT 'X', n_number, alert_id, end_date
                   INTO lv_exists, v_n, v_alert_id, v_end_date
                   FROM table_x
                  WHERE alert_id = :table_x.alert_id
                    AND  n_number = :table_x.n_number
                    AND (TO_CHAR(end_date, 'MM/DD/YYYY') = :table_x.end_date
                     OR  (end_date IS NULL) );
      
              IF lv_exists = 'X'
                    AND v_n IS NOT NULL
                    AND v_alert_id IS NOT NULL
                    AND v_end_date IS  NOT NULL THEN
                       set_alert_property('PAST_WARNING', alert_message_text, 'This alert   occurred in the past. do you still want to enter the recall?');
                       al_button := show_alert('PAST_WARNING');
                  IF al_button = alert_button1 THEN
                           COMMIT;
               END IF;       
             END IF;
      
       EXCEPTION
              
        WHEN NO_DATA_FOUND THEN
                 COMMIT;

        WHEN OTHERS THEN
       RAISE form_trigger_failure;    
    END;

END;