Home » Developer & Programmer » Forms » timesheet problem
timesheet problem [message #223308] Thu, 08 March 2007 07:27 Go to next message
sophia_786
Messages: 13
Registered: February 2007
Location: manchester
Junior Member

Hi all

im creating a timesheet system, and ive created the tables so that

a single timesheet has a number of timesheet items.

so the columns similar to:

timesheet
timesheet week_ending emp_id
1 02-mar-2007 3
2 23-feb-2007 3
3 16-feb-2007 3
4 23-feb-2007 2
5 02-mar-2007 2

timesheet_items
timsheet_item timsheet project total_hours
1 1 Developement 4
2 1 Support 5
3 1 Maintenance 4

i have create sequences to generate the timesheet_item and timesheet values. when a user logs in to fill in a timesheet they are taken directly to the timesheet_item form where they can start to fill in the their hours.
however my problem is that i need the sequence to generate a new timesheet every week not everyime a user logs in.

is this possible?

hope i have explained that ok.

thanks
Re: timesheet problem [message #223310 is a reply to message #223308] Thu, 08 March 2007 07:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could create a stored procedure which would alter sequence (and make its starting value = 1) (you'd need to use EXECUTE IMMEDIATE to do that) and schedule this procedure to run once a week (using DBMS_JOB or DBMS_SCHEDULER).
Re: timesheet problem [message #223378 is a reply to message #223308] Thu, 08 March 2007 13:33 Go to previous messageGo to next message
sophia_786
Messages: 13
Registered: February 2007
Location: manchester
Junior Member

thanks, but im still unsure how to go about doing that can you explain a little more in depth please im very new to oracle forms.

thnaks

Re: timesheet problem [message #223402 is a reply to message #223378] Thu, 08 March 2007 15:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What I suggested has nothing to do with Oracle Forms.

Procedure might look like this (imagine that sequence starts with 1, increments by 1, and has all other default values) - alter a sequence to start with 1 again:
CREATE OR REPLACE PROCEDURE Prc_Alter_Seq IS
  l_last_value NUMBER;
BEGIN
  SELECT seq_test.CURRVAL - 1 INTO l_last_value FROM dual; 
  EXECUTE IMMEDIATE('alter sequence seq_test increment by ' || -l_last_value);
  SELECT seq_test.NEXTVAL INTO l_last_value FROM dual;
  EXECUTE IMMEDIATE('alter sequence seq_test increment by 1');
END;
/
Now that you have it, just schedule this procedure to run once a week. You know the keywords: DBMS_JOB and/or DBMS_SCHEDULER. Check the documentation and find some examples of how to schedule a job.
Re: timesheet problem [message #225419 is a reply to message #223402] Mon, 19 March 2007 22:33 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Previous Topic: how save master and detail form same time
Next Topic: Oracle Forms book
Goto Forum:
  


Current Time: Sat Dec 10 10:59:50 CST 2016

Total time taken to generate the page: 0.03997 seconds