 Sysdate in where clause [message #452461] |
Thu, 22 April 2010 04:47  |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
Hi there
Hope someone can give me a hand. I am not strong in SQL but can write easy SQL for data extraction but seem to not understand how to correctly use sysdate in a where clause.
Case: I have to create an alert that will email my IT dept once a person is terminated in Oracle HR. The alert will only run once a day with all terminations specified in the alert. Setting up the alert is not the issue, but rather the SQL code I want to use.
The alert will run everyday at CoB 17:30. Now, in my query, how do i specify that the results should be only for the current day? The problem however, is that I also retrieve Person Type which should show as Ex-Employee, but this is only shown the day after the actual termination has been done, because the employee is still active on the date of termination.
Thanking you in advance
JP
[Updated on: Thu, 22 April 2010 04:49] Report message to a moderator |
|
|
|
| Re: Sysdate in where clause [message #452464 is a reply to message #452461] |
Thu, 22 April 2010 04:53  |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's easier for all concerned if you post the SQL directly in the thread using code tags like this:
select distinct papf.EMPLOYEE_NUMBER "Employee Number",
papf.title || ' ' ||papf.first_name || ' ' || papf.last_name "Full Name",
ppt.USER_PERSON_TYPE "Employee Status",
haou.NAME "Department",
pps.ACTUAL_TERMINATION_DATE "Actual Terminatin Date",
lvl.meaning "Reason for Leaving"
from hr.per_all_people_f papf,
hr.per_all_assignments_f paaf,
per_assignment_status_types past,
hr.hr_all_organization_units haou,
hr.per_person_types ppt,
hr.per_periods_of_service pps,
apps.fnd_lookup_values_vl lvl
where papf.PERSON_ID = paaf.PERSON_ID
and paaf.ORGANIZATION_ID = haou.ORGANIZATION_ID
and papf.PERSON_TYPE_ID = ppt.PERSON_TYPE_ID
and papf.PERSON_ID = pps.PERSON_ID
and papf.employee_number != 700054
and pps.LEAVING_REASON = lvl.LOOKUP_CODE
and lvl.LOOKUP_TYPE = 'LEAV_REAS'
--and pps.actual_termination_date = sysdate
and trunc(sysdate) between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
and ppt.USER_PERSON_TYPE = 'Ex-employee'
Isn't the type set to EX-employee at the same time the EFFECTIVE_END_DATE is set?
|
|
|
|
| Re: Sysdate in where clause [message #452467 is a reply to message #452464] |
Thu, 22 April 2010 04:57 |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
Hi CM
It is set at the same time, however, if a person is terminated, the type "Ex-employee" will only show the day after the actual termination as the record is still seen as an active record with type"Employee" assigned on the date of the termination
My thinking was to indicate in the where clause that the sysdate, should be tomorrow's date (sysdate,1), but run today! And need help in writing it correctly
The reason for this is that my lead time from the IT dept is 24 hours to affect changes to other systems to keep up with compliance issues once a person is terminated as an employee
JP
|
|
|
|
|
|
| Re: Sysdate in where clause [message #452473 is a reply to message #452469] |
Thu, 22 April 2010 05:27 |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
I stand to be corrected, but no, I cannot ignore the "Ex-Employee" type as this identifies the records I am after. Would the below be more to the correct where clause?
and pps.actual_termination_date = sysdate, getdate()+1 |
|
|
|
| Re: Sysdate in where clause [message #452478 is a reply to message #452461] |
Thu, 22 April 2010 05:42 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I think the problem here is more logical than SQL.
Lets clarify a few things.
1) You want to report employees that have been terminated on the day of termination.
2) The employee status isn't set to Ex-employee until the day after termination.
3) At what point is actual_termination_date set?
4) At what point is EFFECTIVE_END_DATE set?
5) Are those 2 dates the same or different?
6) What's getdate that you've refered to in your last post?
[Updated on: Thu, 22 April 2010 05:43] Report message to a moderator |
|
|
|
| Re: Sysdate in where clause [message #452481 is a reply to message #452478] |
Thu, 22 April 2010 05:52 |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
Quote:
Lets clarify a few things.
1) You want to report employees that have been terminated on the day of termination. Correctly understood
2) The employee status isn't set to Ex-employee until the day after termination. Correct
3) At what point is actual_termination_date set? This can be set at anytime, but this date is the actual date on which an employee resigns, i.e. last working day
4) At what point is EFFECTIVE_END_DATE set? this date is set as soon as there is an update on an employees' record, there will thus be multiple rows in the DB for a particular employee record
5) Are those 2 dates the same or different? These dates are different. The trunc(sysdate) will ensure that I only get the latest record in the DB for an employee record
6) What's getdate that you've refered to in your last post?that should return the current DB system time stamp
I have tried the following but also with no luck
and pps.actual_termination_date = sysdate, getdate()+1
and sysdate = getdate()+1 |
|
|
|
| Re: Sysdate in where clause [message #452483 is a reply to message #452461] |
Thu, 22 April 2010 05:59 |
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Well if the status isn't set till the day after then as far as I can see you have 2 choices:
1) Ignore status and just run off actual_termination_date.
2) Accept that you're only going to get the employees that have been terminated the day after it happens.
Not a SQL issue, it's a logical timing issue, the data simply isn't available when you want it.
Still confused by getdate, sysdate gets the current date/time so what does getdate do that's different?
|
|
|
|
| Re: Sysdate in where clause [message #452487 is a reply to message #452483] |
Thu, 22 April 2010 06:20 |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
I do understand my two options, I just can't fathom that this is not possible as the status will be changed once you date track a day into the future
with the getdate, i suppose i am just trying my luck at anything, like you said: the same data!
Will change the SQL to not include the type.
Thank you for your help CM! Really appreciate it
|
|
|
|
| Re: Sysdate in where clause [message #452501 is a reply to message #452487] |
Thu, 22 April 2010 07:19 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
I am not sure whether the understanding is correct, But here are my assumptions -
Assuming, Employee last working day (or will be terminated on ) = 04/22/2010
Set the actual_termination_date = 04/22/2010
Set ppt.USER_PERSON_TYPE = 'Ex-employee' (this will be set on the next day i.e. on 04/23/2010)
Consider you are running your report today i.e. 04/22/2010 - you expect that this employee should be reported ?
So now considering these : Here are the facts
trunc(SYSDATE) = 04/22/2010
actual_termination_date = 04/22/2010
ppt.USER_PERSON_TYPE = 'Employee'
The logic you are trying to use is and actual_termination_date = sysdate + 1. But how is this going to work when you already have actual_termination_date = 04/22/2010 ??
How do you expect this condition to work ? It never will !
and actual_termination_date = trunc(sysdate)
and ppt.USER_PERSON_TYPE = 'Ex-employee'
This is what cookiemonster has mentioned stating "it is more a logical issue." Both are conflicting conditions, while one is satisfied the other simply won't.
Next day (04/23/2010) or SYSDATE+1 will give you SYSDATE= 04/23/2010
So even if ppt.USER_PERSON_TYPE = 'Ex-employee' on this day, the actual_termination_date does not match and you won't get results.
You may want to take a look at this though :
and (trunc(actual_termination_date) = trunc(sysdate) OR ppt.USER_PERSON_TYPE = 'Ex-employee' )
Note that, this will report your employee on both days.
Today it will give you
actual_termination_date = 04/22/2010
USER_PERSON_TYPE = 'Employee'
Tomorrow it will give you
actual_termination_date = 04/22/2010
USER_PERSON_TYPE = 'Ex-employee'
|
|
|
|
|
|
| Re: Sysdate in where clause [message #454011 is a reply to message #452512] |
Mon, 03 May 2010 07:43 |
JPMarais
Messages: 7
Registered: April 2010
Location: South Africa
|
Junior Member |
|
|
Hi All
after a few days off and some discussions, the code actually works as intended with a few changes
and trunc(sysdate+1) between trunc(papf.effective_start_date) and trunc(papf.effective_end_Date)
The code now allows me to run the query today once a termination is done with the required data
select distinct papf.employee_number "Employee Number",
papf.title || ' ' ||papf.first_name || ' ' || papf.last_name "Full Name",
Hr_Person_Type_Usage_Info.get_user_person_type(actual_termination_date+1,papf.person_id) "Employee Status",
haou.name "Department",
pps.actual_termination_date "Actual Termination Date",
lvl.meaning "Reason for Leaving"
from hr.per_all_people_f papf,
hr.per_all_assignments_f paaf,
hr.hr_all_organization_units haou,
hr.per_periods_of_service pps,
apps.fnd_lookup_values_vl lvl
where papf.person_id = paaf.person_id
and paaf.organization_id = haou.organization_id
and papf.person_id = pps.person_id
and papf.employee_number != 700054
and pps.leaving_reason = lvl.lookup_code
and lvl.lookup_type = 'LEAV_REAS'
and trunc(sysdate+1) between trunc(papf.effective_start_date) and trunc(papf.effective_end_Date)
and pps.actual_termination_date = trunc(sysdate)
and pps.actual_termination_date+1 between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date)
Thank you for all your help
|
|
|
|
| Re: Sysdate in where clause [message #454022 is a reply to message #454011] |
Mon, 03 May 2010 09:33 |
knw15pwr
Messages: 134
Registered: March 2010
|
Senior Member |
|
|
Hi JP
Do you realise that you have just ignored the "Ex-employee" status. Which you very vehemently refused to do so in the first place ?
"JPMarais wrote on Thu, 22 April 2010 05:27"
I stand to be corrected, but no, I cannot ignore the "Ex-Employee" type as this identifies the records I am after.
"cookiemonster wrote on Thu, 22 April 2010 05:59"
..1) Ignore status and just run off actual_termination_date...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
