Home » Developer & Programmer » Forms » Timing conflict (form 10 g)
Timing conflict [message #434233] Tue, 08 December 2009 00:06 Go to next message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
i have form that the user(id) can register in training... so i have training date and start timing and end timing of the training, the problem that i face is if the user has training from 1:00 till 3:00 for 2 days and the same user try to register in next day from 12:00 till 4:00 ...should the system give him message like(can not register in the same timing), i wrote code that the system give me message when the user has training from 1:00 till 3:00 for 2 days and next day try to register from 2:00 till 5:00 ...the system give the user message ...so my problem is when the user register in timing not between start time and end time of the training.

the code is :
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;

[EDITED by LF: reformatted code and applied [code] tags]

[Updated on: Tue, 08 December 2009 06:11] by Moderator

Report message to a moderator

Re: Timing conflict [message #434264 is a reply to message #434233] Tue, 08 December 2009 04:02 Go to previous messageGo to next message
arni111
Messages: 6
Registered: December 2009
Junior Member

Have you ever try to use the between clause ?
Re: Timing conflict [message #434276 is a reply to message #434264] Tue, 08 December 2009 05:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
arni111 wrote on Tue, 08 December 2009 10:02
Have you ever try to use the between clause ?


I can see four in the posted code.

@laith - I'm sure you've been asked before to use code tags, can you please do so because what you've posted is hard to read - see the orafaq forum guide if you're not sure how.
Also can you explain in more detail what data works with your query and what doesn't.
Re: Timing conflict [message #437524 is a reply to message #434233] Mon, 04 January 2010 22:44 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: Timing conflict [message #437528 is a reply to message #437524] Mon, 04 January 2010 23:02 Go to previous message
laith
Messages: 41
Registered: December 2008
Location: U.A.E
Member
it solved.
Previous Topic: OC4J vanished and not working
Next Topic: change despreatly the splashscreen, background and logo
Goto Forum:
  


Current Time: Sun Sep 25 17:59:46 CDT 2016

Total time taken to generate the page: 0.11087 seconds