script for send alert email to itsupport when new employee joining
the below script for send alert email to itsupport when new employee joining
the HR team will update the new employee details in HRMS and after if we schedule the alert, it will send alert to itsupport team for prepare the email/ext/laptop and others.
SELECT Distinct ppf.employee_number, ppf.full_name emp_name,
hr_general.decode_job (paf.job_id) POSITION,
xxhr_ace.get_dept_name (paf.organization_id,
paf.business_group_id
) department,
TO_CHAR (ppf.effective_start_date, ‘DD-MON-YYYY’) Joining_date,
hl.location_code,(select full_name from per_all_people_f where person_id=paf.SUPERVISOR_ID AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date)Supervisor_name,fu.user_name
into &emp_num,&emp_name, &posi, &department,&Joining ,&Loc,&Sup,&UsrName
FROM per_all_people_f ppf,
per_all_assignments_f paf,
per_qualifications pq,
hr_locations hl,
fnd_user fu
WHERE 1 = 1
AND ppf.person_id = paf.person_id
AND ppf.business_group_id = paf.business_group_id
and paf.location_id=hl.location_id
AND pq.person_id(+) = ppf.person_id
and ppf.CREATION_DATE>=trunc(sysdate)
and fu.EMPLOYEE_ID(+)=ppf.PERSON_ID
AND ppf.last_update_date> TO_DATE(:DATE_LAST_CHECKED, ‘DD-MON-YYYY HH24:MI:SS’)
and not exists(select 1 from per_all_people_f where person_id=ppf.person_id and TRUNC (SYSDATE)-365 BETWEEN effective_start_date
AND effective_end_date )
ORDER BY Location_code,TO_NUMBER (ppf.employee_number);
DBA 