Oracle Alerts Event & Periodic

Event Alert

Event Alert Query format                                       

select  EMPLOYEE_NUMBER,

        FULL_NAME,

        designaion,

        DATE_OF_HIRE,

        service_year servc

   into  &emp_no,

         &emp_name,

         &designation,

         &date_of_hire,

         &service_year

 from  (

 select  papf.employee_number,

         papf.full_name,

         pp.NAME  designaion,               

        papf.ORIGINAL_DATE_OF_HIRE  DATE_OF_HIRE,

        round(floor(months_between(trunc(last_day(sysdate)),papf.ORIGINAL_DATE_OF_HIRE))/12) service_year,

        round(floor(months_between(trunc(last_day(sysdate)),papf.DATE_OF_BIRTH))/12) no_years

from   per_people_x papf,

       per_assignments_x paf,

       hr_locations hl,

       PER_POSITIONS PP ,

       per_position_definitions ppd,

      PER_PERIODS_OF_SERVICE pposv

where papf.PERSON_ID = pposv.PERSON_ID

and   paf.PERSON_ID  = papf.PERSON_ID

and   paf.LOCATION_ID = hl.LOCATION_ID

and   paf.POSITION_ID = pp.POSITION_ID

and   pp.POSITION_ID  = ppd.POSITION_DEFINITION_ID

and   ppd.ENABLED_FLAG = 'Y'

and   pposv.ACTUAL_TERMINATION_DATE is null

) where  no_years=57

Output Format

                       IBRAHIM FIBERS LTD.

Following are the list of employees approaching to 57 years in current month.

========================================================================================================

 Emp NO       Name                     Designation                     Date of Hire          Service/Age 

========================================================================================================

=**= Enter summary template below this line =**=

**

&emp_no       &emp_name                 &designation                   &date_of_hire         &service_year

=**= Enter summary template above this line =**=

End of Alert

Periodic Alert

Query Format 

select   ppf.EMPLOYEE_NUMBER,

         ppf.FULL_NAME,

         ppd.SEGMENT1 position,

         ppd.SEGMENT2 department,

         dhw.DATE_WORKED,

         sum(dhw.HOURS) hours 

         into 

         &emp_no,&emp_name,&designation,

         &org,&dat,&hrs       

from     per_people_x ppf,

         per_assignments_x paf,

         per_positions pp,

         per_position_definitions ppd,

         HXT_DET_HOURS_WORKED_f dhw

where    paf.PERSON_ID              =  ppf.PERSON_ID

and      paf.POSITION_ID            =  pp.POSITION_ID

and      pp.POSITION_DEFINITION_ID  =  ppd.POSITION_DEFINITION_ID

and      dhw.ASSIGNMENT_ID          =  paf.ASSIGNMENT_ID 

and      dhw.ELEMENT_TYPE_ID        =  243

and      dhw.DATE_WORKED            =  trunc(sysdate)-1

group by  ppf.EMPLOYEE_NUMBER,ppf.FULL_NAME,ppd.SEGMENT1 ,ppd.SEGMENT2 ,dhw.DATE_WORKED

having    sum(dhw.HOURS)>8

Output format

                       IBRAHIM FIBERS LTD.

Following are the list of Alert for employee cpl hours exceed more than eight hours.

==================================================================================================================================

 Emp NO       Name                     Designation                     Org.Element                            Date           Hours

==================================================================================================================================

=**= Enter summary template below this line =**=

**

&emp_no       &emp_name                 &designation                   &org_element                           &dat            &hrs

=**= Enter summary template above this line =**=

End of Alert