troubleshooting Question

inserting data into a temp table for some validations

Avatar of anumoses
anumosesFlag for United States of America asked on
Oracle Database
7 Comments1 Solution468 ViewsLast Modified:
select count(*) into v_count from dept_staff
            where schedule_date = :dept_staff.schedule_date
              and payroll_id = :emp_unexcld.payroll_id
              and (
              (
                  to_date(lpad(:dept_staff.start_time,4,'0') || :dept_staff.start_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                              to_date(lpad(end_time,4,'0') || end_ampm || 'M','HHMIAM')
            )
            or
            (
                  to_date(lpad(:dept_staff.end_time,4,'0') || :dept_staff.end_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                        to_date(lpad(end_time,4,'0')||end_ampm||'M','HHMIAM')
            ));
--------------------------------------------
If v_count = 0 then
            insert into temp_dept_staff(payroll_id,schedule_date,start_time,start_ampm,total_hours,end_time,end_ampm)
                             values(:dept_staff.payroll_id,:dept_staff.schedule_date,:dept_staff.start_time,
                                    :dept_staff.start_ampm,:dept_staff.total_hours,
                                    :dept_staff.end_time,:dept_staff.end_ampm);
 
              :system.message_level := '10';
                   commit;
              :system.message_level := '0';  
End if;

-----------------------

      select count(*) into v_count1 from temp_dept_staff
            where schedule_date = :dept_staff.schedule_date
              and payroll_id = :emp_unexcld.payroll_id
              and (
              (
                  to_date(lpad(:dept_staff.start_time,4,'0') || :dept_staff.start_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                              to_date(lpad(end_time,4,'0') || end_ampm || 'M','HHMIAM')
            )
            or
            (
                  to_date(lpad(:dept_staff.end_time,4,'0') || :dept_staff.end_ampm || 'M','HHMIAM') between
                        to_date(lpad(start_time,4,'0')||start_ampm||'M','HHMIAM') and
                        to_date(lpad(end_time,4,'0')||end_ampm||'M','HHMIAM')
            ));
-----------------------------------

if v_count > 0 then
msgbox.show('Conflicting Schedule ', 'You entered conflicting schedule information.'||chr(10)||' The new schedule runs from '||:dept_staff.start_time||:dept_staff.start_ampm ||' to '||:dept_staff.end_time||:dept_staff.end_ampm||'.' ||chr(10)||'The previous schedule runs from '||v_start_time||v_start_ampm||' to '||v_end_time||v_end_ampm, 'ERROR');
          go_item('reason_code');
     raise form_trigger_failure;
Elsif v_count1 > 1 then            
msgbox.show('Conflicting Schedule ', 'You entered conflicting schedule information.'||chr(10)||' The new schedule runs from '||:dept_staff.start_time||:dept_staff.start_ampm ||' to '||:dept_staff.end_time||:dept_staff.end_ampm||'.' ||chr(10)||'The previous schedule runs from '||v_start_time||v_start_ampm||' to '||v_end_time||v_end_ampm, 'ERROR');
     go_item('reason_code');
     raise form_trigger_failure;
End if;

The reason I am inserting into a temp table is the user enters the data and on the 2nd line by mistake they may type in the same schedule date and same start time, and at this point I have to show the conflicting message.

The problem is I am committing as I insert into the temp table which also does a commit on the databasae table dept_staff. I do not want the data to get inserted in dept_staff table until the user commits. Any solutions?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros