Home » Developer & Programmer » Forms » req solution (form 6i and oralce 9i enterprize edition 9.2.0.1.0)
req solution [message #377095] Sat, 20 December 2008 02:09 Go to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Dear Members
I am working on attendance system. I want that employee is automatically mark out by system after the 16 hours after he came in if he did not mark out.

suppose emloyee code 1033 employee come in at 09:00 and he did not make attendance for out. The system will automatically out this employee after 16 hours.
The attendance table is as like this
emp_code varchar2(6),
atten_date date,
time_in date,
time_out date

Thanks & regards
Re: req solution [message #377097 is a reply to message #377095] Sat, 20 December 2008 02:23 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way would be to submit a job which would run exactly 16 hours after employee signs in; and sign him/her out if necessary. In order to do that, you might research use of database triggers (to run DBMS_JOB), stored procedures (to write a procedure which will check employee's status and sign him/her out) and DBMS_JOB package (to submit a job).
Re: req solution [message #377102 is a reply to message #377095] Sat, 20 December 2008 03:11 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
You can add a column, EMP_SYS_TIME_OUT Date to your table.

emp_code varchar2(6),
atten_date date,
time_in date,
time_out date
EMP_SYS_TIME_OUT Date

Now when a Employee signs in the system you calculate its sign out time i.e. Sign in time + 16 hours.

If you are using oracle forms or any front end, write a validation, employee cannot sign off from system after EMP_SYS_TIME_OUT is crossed.

See if this approach works for you.
Let me know if there are further queries on this.


Re: req solution [message #377110 is a reply to message #377095] Sat, 20 December 2008 05:06 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
@dhanuka

WHEN will the validation be fired? How to know it will fire after 16 hours if he doesn't use JOBS??

Rajy
Re: req solution [message #377113 is a reply to message #377110] Sat, 20 December 2008 05:18 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
We need that if Employee does not sign out after 16 hours system automatically marks him sign off.

There are two approaches to the question mentioned.

1. User database jobs and schedule it on regular intervals or
fixed time, which will check for log out time and then update
according the requirements.

2.
Table Structure:
emp_code varchar2(6),
atten_date date,
time_in date,
time_out date
EMP_SYS_TIME_OUT Date

When user signs on store sign date time + 16 hours in
table(EMP_SYS_TIME_OUT).
When user signs off manually check his system sign off time
(SYSDATETIME).
If (SYSDATETIME) is greater than system sign off time prompt
user a error message
"System sign off done" and update (time_out) column time with
system sign off time(EMP_SYS_TIME_OUT).

I hope this helps.

Dhanuka. Rajesh






Re: req solution [message #377233 is a reply to message #377113] Mon, 22 December 2008 01:01 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for reply

I have done as you say. I have added the column sys_out in my attendance table but i have faced a problem.

emp_code atten_date time_in                time_out                   sys_out
001174  12/22/2008  12/22/2008 9:04:07 AM  12/22/2008 11:50:07 AM    12/23/2008 1:04:07 AM
001174  12/22/2008  12/22/2008 12:50:07 PM  


in our company a employee can come in and out more than once time. I have placed the sys_out time when employee first time come in. But when he go out and again come in i have not placed any value in sys_out field because this value is added only one time when he first time come in. when he second time come in and after he forget to sign out, the system should automatically sighn out to this second rows time_out field.
Please see the above table data will help out to understand the problem.

Thanks and regards

[Updated on: Mon, 22 December 2008 01:46]

Report message to a moderator

Re: req solution [message #377259 is a reply to message #377233] Mon, 22 December 2008 02:59 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
As per your specification,
1. Multiple Sign in & Sign of possible in one day.
2. System to sign off employee based on his sign in time for
last sign on done in system.

Following steps can be taken to avoid complications.
1. Employee cannot sign in system unless signed off for the day.
(Prompt a error message saying cannot sign on as employee has
not signed off)
2. System Sign off time should be updated on each log in.
(With this there can always be only one record where the user
might not have signed off)

I have one query, What sign out time must be used by system.
I.e. Employee signed in system at 12/22/2008 9:04:07 AM
System Out time calculated is 12/23/2008 1:04:07 AM
Now user has signed off at 12/22/2008 11:50:07 AM and
sign in system again at 12/22/2008 12:50:07 PM.

My query is, when employee signs in system at 12/22/2008 12:50:07 PM time what should be the system sign off time?
Otpions.
A - 12/22/2008 12:50:07 PM + 16 hours
B - 12/22/2008 9:04:07 AM + 16 hours
(First sign on time + 16hours)

Note: - Also let me know if there is scope to change table
design at data flow and logical processing level.
(Like we can have head detail tables or separate set of
tables to maintain separate data)



Dhanuka. Rajesh




Re: req solution [message #377280 is a reply to message #377259] Mon, 22 December 2008 03:48 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for reply
As you asked the sys_out time for the employee for second time sign in will be the same as the same as he first sign in.
B option is valid 12/22/2008 9:04:07 AM + 16 hours.

The table changes can be made if necessary.


Thanks and regards
Re: req solution [message #377283 is a reply to message #377280] Mon, 22 December 2008 04:03 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
If user signs on second time,
write a cursor to get System sign out time for that day (first sign on record) and update the system sign out column with that time.

Ok.

This should resolve your issue.

Dhanuka. Rajesh






Re: req solution [message #377415 is a reply to message #377283] Mon, 22 December 2008 22:55 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Yes your solution is quite valid if the employee sign in and sign out in the same day. But it is not valid if an employee has come in current date and has sign out on next day and again sign in next day. See the below diagram
emp_code atten_date time_in                time_out                   sys_out
002277  12/22/2008  12/22/2008 11:04:07 PM  12/23/2008 01:50:07 AM    12/23/2008 3:04:07 PM
002277  12/23/2008  12/23/2008 02:40:07 AM  



My query is
(1) I have placed in sys_out field (time_in+16/24) = 12/23/2008 3:04:07 PM when the employee 1st time sign in. How can i place the same value when employee sign in 2nd or 3rd time. Because i have placed it only one time when the employee first time sign in. It can solve my problem.
(2) How can i place value in sys_out the employee who sign in in current date and sign out in next date and again sign in next date. Because the employee total in time = 8 hours.

thanks & regards



[Updated on: Tue, 23 December 2008 02:01]

Report message to a moderator

Re: req solution [message #378110 is a reply to message #377415] Sun, 28 December 2008 23:30 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Still waiting for reply.


Thanks and regards.
Re: req solution [message #378116 is a reply to message #378110] Mon, 29 December 2008 00:26 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
Select Max(System Sign out time) from table.

If (New sign on time) < (Max(System Sign Out time)) Then

New System Sign Out time = MAx(System Sign Out Time);

Elsif (New Sign on time) >= (Max(System Sign out time)) then

New System Sign Out time = (New System Sign Out time) + 16 hrs.

End If;

This will resolve the issue even if the dates change and sign on and sign off are handled in two diff working dates.

Regards,
Dhanuka. Rajesh

Re: req solution [message #378161 is a reply to message #378116] Mon, 29 December 2008 03:29 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thaks for reply.

Yes this has solve my problem. Now every time when the employee sign in , sys_out time is also added.

Now i want if the employee sign in and his atten_date is 12/22/2008 and time_in is 12/22/2008 11:04:07 PM and he sign out at 12/23/2008 01:50:07 AM and again he sign in (time_in) at 12/23/2008 02:40:07 AM and his atten_date is 12/23/2008. But i want if employee sign in 12/22/2008 his atten_date should always be 12/22/2008 although he again sign in next date 12/23/2008.
Because i have to take min time_in and max(time_out) for atten_date that should be 12/22/2008. Please see the below table
Existing
emp_code atten_date time_in                time_out                   sys_out
002277  12/22/2008  12/22/2008 11:04:07 PM  12/23/2008 01:50:07 AM    12/23/2008 3:04:07 PM
002277  12/23/2008  12/23/2008 02:40:07 AM   



Required

emp_code atten_date time_in                time_out                   sys_out
002277  12/22/2008  12/22/2008 11:04:07 PM  12/23/2008 01:50:07 AM    12/23/2008 3:04:07 PM
002277  12/22/2008  12/23/2008 02:40:07 AM   


Thanks and Regards
Re: req solution [message #378172 is a reply to message #378161] Mon, 29 December 2008 03:57 Go to previous messageGo to next message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
Select MIN(SIGN IN TIME), MAX(SIGN OUT TIME)
From Table
Where EMP_ID = XXX
Group by (SYSTEM SIGN OUT DATE TIME)

This query will return Mininum sign on time and Maximum Sign off time during the day.
I.e 16 hours.

I have some queries:
1. For how many hours a employee works generally.
2. What if user does a double shift, i mean he generally is
suppose to work for X number of hours in a day.

Now he is working for 2X number of hours in same day (Continous)


Best Regards,

Dhanuka. Rajesh




Re: req solution [message #378389 is a reply to message #378172] Tue, 30 December 2008 04:00 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

Thanks for reply

I have placed min(sign_in) + 16/24 in sys_out column when ever employee sign in. Please tell me how can i place (min(sign_in) + 1/1440) in sign out if the employee forget to sign out after 16 hours.


Thanks And Regards
Re: req solution [message #378735 is a reply to message #377095] Thu, 01 January 2009 00:59 Go to previous messageGo to next message
sweetkhaliq
Messages: 189
Registered: April 2006
Senior Member

dhanuka.rajesh Is there any solution.

[Updated on: Thu, 01 January 2009 04:28]

Report message to a moderator

Re: req solution [message #379385 is a reply to message #378735] Tue, 06 January 2009 04:55 Go to previous message
dhanuka.rajesh
Messages: 49
Registered: March 2008
Location: Mumbai
Member
Please give details for your query...

I am sorry for the inconvenience caused as i was out of town not able to check my mails.


Regards,

Dhanuka. Rajesh
Previous Topic: I need a sample application in oracle forms
Next Topic: Fetch more than one record in cursor
Goto Forum:
  


Current Time: Thu Dec 08 16:45:42 CST 2016

Total time taken to generate the page: 0.13546 seconds