OraFAQ Forum: Forms » timesheet problem

Home » Developer & Programmer » Forms » timesheet problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

timesheet problem [message #223308]

Thu, 08 March 2007 07:27

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

Report message to a moderator

Re: timesheet problem [message #223310 is a reply to message #223308]

Thu, 08 March 2007 07:40

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: timesheet problem [message #223378 is a reply to message #223308]

Thu, 08 March 2007 13:33

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

Report message to a moderator

Re: timesheet problem [message #223402 is a reply to message #223378]

Thu, 08 March 2007 15:18

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: timesheet problem [message #225419 is a reply to message #223402]

Mon, 19 March 2007 22:33

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Have you solved your problem?

David

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 21:50:58 CST 2025