DECLARE
  vcount   NUMBER;
Begin
  IF :pypstsd_status_ind = 'R'
  THEN
    SELECT count (*)
      into vcount
      FROM pypstsm, pypstsd
     WHERE pypstsd_ref_no = pypstsm_ref_no
       AND pypstsm_ref_no <> :pypstsm_ref_no
       AND pypstsd_participant_pidm IN (SELECT pypstsd_participant_pidm
                                          FROM pypstsd
                                         WHERE pypstsd_ref_no = :pypstsm_ref_no)
       AND (   :pypstsm_date BETWEEN pypstsm_date
                                 AND (((pypstsm_date + pypstsm_no_of_days) - 1
                                      )
                                     )
            OR (:pypstsm_date + :pypstsm_no_of_days) - 1 BETWEEN pypstsm_date
                                                             AND ((  (  pypstsm_date
                                                                      + pypstsm_no_of_days
                                                                     )
                                                                   - 1
                                                                  )
                                                                 )
           )
       AND (   TO_DATE (   TO_CHAR (:pypstsm_date, 'DD-MON-YY')
                        || ' '
                        || TO_CHAR (:pypstsm_start_time, 'HH24:MI'),
                        'DD-MON-YY HH24:MI'
                       ) BETWEEN TO_DATE (   TO_CHAR (:pypstsm_date,
                                                      'DD-MON-YY')
                                          || ' '
                                          || TO_CHAR (pypstsm_start_time,
                                                      'HH24:MI'
                                                     ),
                                          'DD-MON-YY HH24:MI'
                                         )
                             AND TO_DATE (   TO_CHAR (:pypstsm_date,
                                                      'DD-MON-YY')
                                          || ' '
                                          || TO_CHAR (pypstsm_end_time,
                                                      'HH24:MI'
                                                     ),
                                          'DD-MON-YY HH24:MI'
                                         )
            OR TO_DATE (   TO_CHAR (:pypstsm_date, 'DD-MON-YY')
                        || ' '
                        || TO_CHAR (:pypstsm_end_time, 'HH24:MI'),
                        'DD-MON-YY HH24:MI'
                       ) BETWEEN TO_DATE (   TO_CHAR (:pypstsm_date,
                                                      'DD-MON-YY')
                                          || ' '
                                          || TO_CHAR (pypstsm_start_time,
                                                      'HH24:MI'
                                                     ),
                                          'DD-MON-YY HH24:MI'
                                         )
                             AND TO_DATE (   TO_CHAR (:pypstsm_date,
                                                      'DD-MON-YY')
                                          || ' '
                                          || TO_CHAR (pypstsm_end_time,
                                                      'HH24:MI'
                                                     ),
                                          'DD-MON-YY HH24:MI'
                                         )
           )
       AND pypstsd_status_ind = 'R'
       AND pypstsm_active_ind = 'Y';

    if vcount > 0
    then
      message ('*ERROR* this person already registered on another Training.');
      message ('*ERROR* this person already registered on another Training.');
      RAISE form_trigger_failure;
    end if;
  END IF;
end;