Whenever we talk about Date Tracking in EBS, we always refer to the effective_start_date and effective_end_date fields. Does code like this look familiar?
SELECT *
FROM per_all_people_f papf
WHERE sysdate between papf.effective_start_date and papf.effective_end_date
While this great for making sure you have the record on the date you are looking for, I find it to be only part of the solution. We also might look at the things like the lf_evt_ocrd_dt. This tells me when the Life Event in EBS occurred.
There are often times however when this date will cause some problems. This typically occurs when something is backdated. You may be looking for an action to occur on a particular date. If your functional users are late to the game, chances are, a few days could pass by and that date has passed. How do you address that you ask? Look at the Last_Update_Date field. This field will tell when the record was update no matter when the lf_evt_ocrd_dt date is.
Take a look at the SQL statement below. This is comes from an Oracle EBS Alert to notify other team that a new employee has been hired and they need a network log on account. My initial implementation of this Alert assumed that the Life Events were being entered on the actual date, and running daily would email everybody about the new hire. Take note of the highlighted line of code below. WRONG!!! This of course resulted in a number of New Hires getting missed by the alert. By replacing that line with: and trunc(sysdate) = trunc(bplfp.last_update_date) I was able to capture the date the row was updated even if the NEW HIRE life event was back dated. Here is the output of this query with the two dates side by side.
Notice the varying dates of the lf_evt_ocrd_dt field versus the consistency of the last_update_date.
So if you find that sometimes you are missing data in your reports, alerts, or etc, take a look at the Last_Update_Date field. You might just find what you are looking for.
SELECT DISTINCT (papf.employee_number)
, papf.full_name
, papf.email_address
, substr (hpf.name,10)
, haou.name
, bplfp.person_id
, bplfp.ler_id
, bplfp.lf_evt_ocrd_dt
, bplfp.last_update_date
, blf.name
FROM ben_ptnl_ler_for_per bplfp
, per_all_people_f papf
, ben_ler_f blf
, per_all_assignments_f paaf
, hr_positions_f hpf
, hr.hr_all_organization_units haou
WHERE 1=1
and papf.person_id=paaf.person_id
and hpf.position_id=paaf.position_id
and haou.organization_id=hpf.organization_id
and bplfp.person_id = papf.person_id
and bplfp.ler_id = blf.ler_id(+)
and sysdate between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date)
and trunc(sysdate) between trunc(hpf.effective_start_date) and trunc(hpf.effective_end_date)
and trunc(sysdate) = trunc(bplfp.lf_evt_ocrd_dt)
and blf.name = ‘NEW HIRE’
The code does work, so if you need it, you are free to use it.
Enjoy!
