Business Alert/User Query Samples

SELECT

[HOLIDAY]=HOLIDAY_DATE,

[DESCRIPTION]=HOLIDAY_DESC

FROM PLHOLIDAY WHERE YEAR(GETDATE())=YEAR(HOLIDAY_DATE) AND MONTH(GETDATE())=MONTH(HOLIDAY_DATE)

SELECT

[EE NAME]=E.SURNAME+' '+E.OTHERNAME,

L.LEAVE_FR,

L.LEAVE_TO,

L.DAYS,

L.LEAVE_CODE

FROM PLLEAVE L

INNER JOIN v_Employee E ON E.EE_NO=L.EE_NO

WHERE DATEDIFF(D,LEAVE_FR,GETDATE())=0 OR (DATEDIFF(D, LEAVE_FR,GETDATE())>=0 AND DATEDIFF(D, LEAVE_TO,GETDATE())<=0)

ORDER BY L.EE_NO

SELECT

L.EE_NO,

E.DEPT_CODE,

EE_NAME = DBO.EENAME(L.EE_NO),

L.LEAVE_DATE,

L.LEAVE_CODE 

FROM plLeaveDayRecord L

INNER JOIN v_Employee E ON E.EE_NO=L.EE_NO

WHERE L.LEAVE_DATE>=DATEADD(DAY,6+((DATEPART(WEEKDAY,dbo.Today())-2)*-1),dbo.Today())

AND L.LEAVE_DATE<=DATEADD(DAY,12+((DATEPART(WEEKDAY,dbo.Today())-2)*-1),dbo.Today())

ORDER BY LEAVE_DATE,L.EE_NO

SELECT 

Last_Backup_DateTime=MAX(backup_finish_date),

Days_before=DATEDIFF(D,MAX(backup_finish_date), GETDATE())

FROM msdb.dbo.backupmediafamily m

INNER JOIN msdb.dbo.backupset b ON m.media_set_id=b.media_set_id

WHERE b.database_name = N'dbHRPro'

SELECT

[HOLIDAY]=HOLIDAY_DATE,

[DESCRIPTION]=HOLIDAY_DESC

FROM PLHOLIDAY

WHERE REGION='HKG' AND DATEDIFF(d,GETDATE(),HOLIDAY_DATE)=3

SELECT

EE_NO,

FULLNAME=DBO.EENAME(EE_NO),

DEPT_CODE,

TERMN_DATE,

REASON_CODE,

TERMN_REASON

FROM v_Employee 

WHERE DATEDIFF(d,GETDATE(),TERMN_DATE)>=0 AND DATEDIFF(d,GETDATE(),TERMN_DATE)<=3

ORDER BY TERMN_DATE

SELECT

EE_NO,

FULLNAME=DBO.EENAME(EE_NO),

DEPT_CODE,

NEXT_APPRAISAL_DATE

FROM v_Employee 

WHERE dbo.IsActiveEmployeeOn(EE_NO,GETDATE())=1 AND

DATEDIFF(d,GETDATE(),NEXT_APPRAISAL_DATE)>=0 AND DATEDIFF(d,GETDATE(),NEXT_APPRAISAL_DATE)<=14

ORDER BY NEXT_APPRAISAL_DATE

SELECT

EE_NO,

EE_NAME,

DEPT_CODE,

NEXT_BIRTHDATE=dbo.NextAnniversaryDate(BIRTHDATE,GETDATE())

FROM v_Employee 

WHERE AUTHORIZED=1 

AND DATEDIFF(d,GETDATE(),dbo.NextAnniversaryDate(BIRTHDATE,GETDATE()))<=2

ORDER BY dbo.NextAnniversaryDate(BIRTHDATE,GETDATE())

SELECT

[DATE]= (CASE WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=0 THEN 'Today'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=1 THEN 'Tomorrow'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=2 THEN '2 days later'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=3 THEN '3 days later'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=4 THEN '4 days later'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=5 THEN '5 days later'

WHEN DATEDIFF(d,GETDATE(), D.[HR_Date])=6 THEN '6 days later' END), [LEAVE_DATE]=D.[HR_Date],

[Day of Week]=ISNULL((SELECT Holiday_Desc FROM plHoliday WHERE [PUBLIC]=1 AND REGION='HKG' AND HOLIDAY_DATE=D.[HR_Date]),datename(weekday,D.[HR_Date])),

[Annual Leave]=dbo.ALNamesAt(D.[HR_Date]),

[Sick Leave]=dbo.SLNamesAt(D.[HR_Date]),

[Other Leave]=dbo.OLNamesAt(D.[HR_Date])

FROM [plDate] D

WHERE DATEDIFF(d,GETDATE(), D.[HR_Date])>=0

AND DATEDIFF(d,GETDATE(),D.[HR_Date])<=6

SELECT L.[UserID]

      ,[EventDateTime]=convert(nvarchar(12),L.EventDateTime,111) + ' ' + convert(nvarchar(12),L.EventDateTime, 114)

      ,L.[BATCH_NO]

      ,L.[EE_NO]

      ,[EE_NAME]=dbo.EENAME(L.[EE_NO])

      ,L.[Field]

      ,L.[OriginalValues]

      ,L.[NewValues]

  FROM [plPayrollChangeLog] L

  WHERE DATEDIFF(d,GETDATE(),EventDateTime)=-1

SELECT L.[UserID]

      ,L.[EE_NO]

      ,[EventDateTime]=convert(nvarchar(12),L.EventDateTime,111) + ' ' + convert(nvarchar(12),L.EventDateTime, 114)

      ,[EE_NAME]=dbo.EENAME(L.[EE_NO])

      ,L.[Field]

      ,L.[OriginalValues]

      ,L.[NewValues]

  FROM [plEEChangeLog] L

  WHERE DATEDIFF(d,GETDATE(),EventDateTime)=-1

SELECT

[EE_NO],

[EE Name]=DBO.EENAME(E.EE_NO),

DEPT_CODE,

EE_CLASS,

[CONTRACT_END_DATE]=dbo.fnFormatDate(E.CONTRACT_END_DATE)

FROM v_Employee E

WHERE

DATEDIFF(m, GETDATE(), E.CONTRACT_END_DATE) >= 0 AND DATEDIFF(m, GETDATE(), E.CONTRACT_END_DATE) <= 1

AND dbo.IsActiveEmployeeOn(E.EE_NO,dbo.Today())=1 ORDER BY E.CONTRACT_END_DATE

SELECT   [EMAIL]=dbo.EEEmail(A.NEXT_APPROVER)

        ,[Applicant]=A.EE_NAME

        ,[Apply Date]=A.CREATE_DATE

        ,[Leave Type]=dbo.LeaveTypeDescription(A.LEAVE_CODE)

,[Leave Date]=dbo.LeaveApplyBriefDesc(A.[LEAVE_FR],A.[LEAVE_TO],A.[AMPM],A.[LEAVE_CODE],A.[FR_TIME],A.[TO_TIME],A.[DAYS],A.[HOURS])

,[Status]=A.APPROVED_STS

,[Status Date]=A.STATUS_DATE

,[Pending for]=dbo.GeneralNumericFormat(DATEDIFF(d,A.STATUS_DATE,dbo.Today()))+' days'

  FROM [v_plLeaveApply] A

  WHERE dbo.IsPending(A.APPROVED_STS)=1

  AND dbo.IsExpenseClaims(A.[LEAVE_CODE])=0

  AND DATEDIFF(d,A.STATUS_DATE,dbo.Today()) > 3

  AND dbo.EEEmail(A.NEXT_APPROVER)>''

  ORDER BY dbo.EEEmail(A.NEXT_APPROVER), A.STATUS_DATE

SELECT EMAIL, [Application Date], [Applicant], [Leave from], [To], [Leave Days], [Leave Type],[Apply for]

FROM 

(

SELECT E.EMAIL

        ,[Application Date]=A.CREATE_DATE

        ,[Applicant]=A.EE_NAME

        ,[Leave from]=A.LEAVE_FR

        ,[To]=A.LEAVE_TO

        ,[Leave Days]=A.[DAYS]

        ,[Leave Type]=A.LEAVE_CODE

        ,[Apply for]='Apply'

  FROM [v_plLeaveApply] A

  INNER JOIN plLeaveApply L On A.RowSerial=L.RowSerial

  INNER JOIN v_Employee E ON E.EE_NO=A.NEXT_APPROVER

  WHERE dbo.IsPending(A.APPROVED_STS)=1    

  AND DATEDIFF(d,A.CREATE_DATE,dbo.Today())=0

  AND L.CREATE_DATE=L.LAST_UPDATE_DATE

UNION ALL

SELECT E.EMAIL

        ,[Application Date]=L.LAST_UPDATE_DATE

        ,[Applicant]=A.EE_NAME

        ,[Leave from]=A.LEAVE_FR

        ,[To]=A.LEAVE_TO

        ,[Leave Days]=A.[DAYS]

        ,[Leave Type]=A.LEAVE_CODE

        ,[Apply for]='Edit'

  FROM [v_plLeaveApply] A

  INNER JOIN plLeaveApply L On A.RowSerial=L.RowSerial

  INNER JOIN v_Employee E ON E.EE_NO=A.NEXT_APPROVER

  WHERE dbo.IsPending(A.APPROVED_STS)=1    

  AND DATEDIFF(d,L.LAST_UPDATE_DATE,dbo.Today())=0

  AND L.CREATE_DATE<>L.LAST_UPDATE_DATE

UNION ALL

SELECT E.EMAIL

      ,[Application Date]=C.CREATE_DATE

      ,[Applicant]=dbo.EENAME(C.[EE_NO])

      ,[Leave from]=A.LEAVE_FR

      ,[To]=A.LEAVE_TO

      ,[Leave Days]=A.[DAYS]

      ,[Leave Type]=A.LEAVE_CODE

      ,[Apply for]='Cancel'

  FROM [dbo].[plLeaveApplyCancel] C

  INNER JOIN [v_plCancelRequestApply] V ON V.RowSerial=C.RowSerial

  INNER JOIN [v_plLeaveApply] A ON A.RowSerial=C.plLeaveApply_RowSerial

  INNER JOIN v_Employee E ON E.EE_NO=V.NEXT_APPROVER

  WHERE dbo.IsPending(C.CANCEL_STS)=1

  AND DATEDIFF(d,C.CREATE_DATE,dbo.Today())=0

) S

ORDER BY S.EMAIL, [Application Date]

SELECT [EE_NO]

      ,[START_DATE]

      ,[COST_CENTRE]

      ,[PERCENTAGE]

  FROM [dbo].[plEECostCentre]

  ORDER BY [EE_NO],[START_DATE]

SELECT E.EMAIL

      ,[Employee]=dbo.EEName(A.[EE_NO])

      ,[Date]=A.WORK_DATE

      ,[Work In]=(CASE WHEN A.[IN_TIME1]='' THEN 'Missing' ELSE A.[IN_TIME1] END)

      ,[Work Out]=(CASE WHEN A.[OUT_TIME2]='' THEN 'Missing' ELSE A.[OUT_TIME2] END)

  FROM plATTD A

  INNER JOIN v_Employee E ON E.EE_NO=A.EE_NO

  WHERE DATEDIFF(d,A.WORK_DATE,GETDATE())=1 AND dbo.IsMissedSwipe(A.[WORK_DATE],A.EE_NO,A.IN_TIME1,    A.OUT_TIME1, A.IN_TIME2, A.OUT_TIME2, A.IN_TIME3, A.OUT_TIME3)=1

  ORDER BY E.EMAIL

SELECT [EE No.vr_PLRCT.EE_NO]

      ,[Badge No.vr_PLRCT.BADGE_NO]

      ,[Employee Name.vr_PLRCT.EE_NAME]

      ,[Department.vr_PLRCT.DEPT_NAME]

      ,[Division.vr_PLRCT.DIVISION_NAME]

      ,[Employer Code.vr_PLRCT.EMPLOYER_CODE]

      ,[Work Hours Type.vr_PLRCT.WORK_HRS_TYPE]

      ,[Roster Code.vr_PLRCT.ROSTER_CODE]

      ,[Join Date.vr_PLRCT.JOIN_DATE]

      ,[Weeks of Service.vr_PLRCT.WEEK_OF_SERVICE.N0]

      ,[Week 1.vr_PLRCT.WEEK1.NS]

      ,[Week 1 Working Hours.vr_PLRCT.WEEK1.NS]

      ,[Week 2.vr_PLRCT.WEEK2.NS]

      ,[Week 2 Working Hours.vr_PLRCT.WEEK2.NS]

      ,[Week 3.vr_PLRCT.WEEK3.NS]

      ,[Week 3 Working Hours.vr_PLRCT.WEEK3.NS]

      ,[Week 4.vr_PLRCT.WEEK4.NS]

      ,[Week 4 Working Hours.vr_PLRCT.WEEK4.NS]

      ,[Fulfils 418.vr_PLRCT.Is418]

  FROM [dbo].[vr_rptPLRCT]

-- %NoFooter%

SELECT  [REVIEWER_EMAIL]=dbo.EEEmail([REVIEWER_NO]),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo(EE_NO),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_REVIEW' 

ORDER BY dbo.EEEmail([REVIEWER_NO]),[APPRAISAL_NO]

SELECT  [EMAIL]=dbo.EEEmail([EE_NO]),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo([EE_NO]),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='AWAITING_EMPLOYEE_FEEDBACK' 

ORDER BY dbo.EEEmail([EE_NO]),[APPRAISAL_NO]

SELECT  [EMAIL]=dbo.EEEmail([FEEDBACKER_NO]),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo([EE_NO]),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='AWAITING_FEEDBACKER_FEEDBACK' 

ORDER BY dbo.EEEmail([FEEDBACKER_NO]),[APPRAISAL_NO]

SELECT  [EMAIL]=dbo.EEEmail([REVIEWER_NO]),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo([EE_NO]),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_FINAL_REVIEW' 

ORDER BY dbo.EEEmail([REVIEWER_NO]),[APPRAISAL_NO]

SELECT  [EMAIL]=dbo.HREmail(),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo([EE_NO]),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster M

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_HR_COMMENT' 

ORDER BY dbo.HREmail(),[APPRAISAL_NO]

SELECT 

   E.EMAIL,

   [Employee]=dbo.EENameNo(E.EE_NO),

   [Entitled Date]=C.ADJ_DATE,

   [Entitled Hours]=C.[HOURS],

   [Com Leave Balance]=C.HOURS_BALANCE,

   [Expiry Date]=C.[EXPIRY_DATE]

FROM v_Employee E

CROSS APPLY dbo.ComLeaveBalanceAllocation(E.EE_NO,dbo.Today()) C

WHERE HOURS_BALANCE<>0

AND DATEDIFF(DAY, dbo.Today(), C.[EXPIRY_DATE]) >= 0

AND DATEDIFF(DAY, dbo.Today(), C.[EXPIRY_DATE]) <= 7

SELECT

[EE No.v_Employee.EE_NO]=EE_NO,

[Employee Name.v_Employee.EE_NAME]=EE_NAME,

[Department.v_Employee.DEPT_NAME]=DEPT_NAME,

[Division.v_Employee.DIVISION_NAME]=DIVISION_NAME,

[Leave Type]='Birthday Leave',

[Entitled]=dbo.LeaveTypeEntitlement('BL',EE_NO,dbo.Today(),0),

[Adjustment]=dbo.LeaveAdjustmentByLeaveType('BL',EE_NO,dbo.LeaveTypeStartDate('BL',EE_NO,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),

[Taken]=dbo.LeaveTakenByLeaveType('BL',EE_NO,dbo.LeaveTypeStartDate('BL',EE_NO,dbo.Today()),dbo.Today()),

[Approved]=dbo.LeaveTakenByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),

[Pending]=dbo.LeaveTypePendingApproval(EE_NO, 'BL', 0, dbo.Today()),

[Balance]=dbo.LeaveTypeBalance('BL',EE_NO,dbo.Today())+dbo.LeaveTakenByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.Today()),dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),

[Future Adjustment]=dbo.LeaveAdjustmentByLeaveType('BL',EE_NO,DATEADD(DAY,1,dbo.LeaveTypeEndDate('BL',EE_NO,dbo.Today())),dbo.FutureEndDate(EE_NO,'BL',dbo.Today())),

[Future Balance]=dbo.LeaveTypeBalance('BL',EE_NO,dbo.FutureEndDate(EE_NO,'BL',dbo.Today())),

[Future As Of]=dbo.FutureEndDate(EE_NO,'BL',dbo.Today())

FROM v_Employee E

WHERE E.AUTHORIZED=1

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.EE_NO

WHERE dbo.IsPending(A.[STATUS])=1 

ORDER BY E.EMAIL, A.CREATE_DATE

SELECT

EMAIL,

EE_NAME,

[APPLICATION_TYPE],

[LEAVE_TYPE],

[APPLICATION_DESCRIPTION],

[DAYS/HOURS/AMOUNT],

[APPLY_DATE],

[STATUS]

FROM

(

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO1]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=0  -- Any Approver

UNION 

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO2]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=0  -- Any Approver

UNION 

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO3]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=0  -- Any Approver

UNION 

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO4]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=0  -- Any Approver

UNION 

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO5]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=0  -- Any Approver

) S

ORDER BY EMAIL, [APPLY_DATE]

SELECT

EMAIL,

EE_NAME,

[APPLICATION_TYPE],

[LEAVE_TYPE],

[APPLICATION_DESCRIPTION],

[DAYS/HOURS/AMOUNT],

[APPLY_DATE],

[STATUS]

FROM

(

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO1]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=1  -- All Approver

AND A.APPROVER_NO1 <> A.PARTIALLY_APPROVED_BY

AND A.APPROVER_NO1 <> A.MAJORITY_APPROVED_BY

AND A.APPROVER_NO1 <> A.LARGE_MAJORITY_APPROVED_BY

AND A.APPROVER_NO1 <> A.VAST_MAJORITY_APPROVED_BY

UNION ALL

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO2]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=1  -- All Approver

AND A.APPROVER_NO2 <> A.PARTIALLY_APPROVED_BY

AND A.APPROVER_NO2 <> A.MAJORITY_APPROVED_BY

AND A.APPROVER_NO2 <> A.LARGE_MAJORITY_APPROVED_BY

AND A.APPROVER_NO2 <> A.VAST_MAJORITY_APPROVED_BY

UNION ALL

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO3]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=1  -- All Approver

AND A.APPROVER_NO3 <> A.PARTIALLY_APPROVED_BY

AND A.APPROVER_NO3 <> A.MAJORITY_APPROVED_BY

AND A.APPROVER_NO3 <> A.LARGE_MAJORITY_APPROVED_BY

AND A.APPROVER_NO3 <> A.VAST_MAJORITY_APPROVED_BY

UNION ALL

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO4]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=1  -- All Approver

AND A.APPROVER_NO4 <> A.PARTIALLY_APPROVED_BY

AND A.APPROVER_NO4 <> A.MAJORITY_APPROVED_BY

AND A.APPROVER_NO4 <> A.LARGE_MAJORITY_APPROVED_BY

AND A.APPROVER_NO4 <> A.VAST_MAJORITY_APPROVED_BY

UNION ALL

SELECT 

EMAIL=E.EMAIL,

EE_NAME=A.[NAME],

[APPLICATION_TYPE]=dbo.ApplicationType(A.LEAVE_CODE)+' Application',

[LEAVE_TYPE]=dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION]=[LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT]=[DAYS_HOURS],

[APPLY_DATE]=dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.[APPROVER_NO5]

WHERE dbo.IsPending(A.[STATUS])=1 

AND ApprovalWorkFlow=1  -- All Approver

AND A.APPROVER_NO5 <> A.PARTIALLY_APPROVED_BY

AND A.APPROVER_NO5 <> A.MAJORITY_APPROVED_BY

AND A.APPROVER_NO5 <> A.LARGE_MAJORITY_APPROVED_BY

AND A.APPROVER_NO5 <> A.VAST_MAJORITY_APPROVED_BY

) S

ORDER BY EMAIL, [APPLY_DATE]

SELECT E.EMAIL,

       [Employee]=dbo.EENameNo(E.EE_NO),

       [Notice Subject]=N.[NB_SUBJECT],

       [Notice Date]=N.[NB_DATE],

       [Posted by]=dbo.EEName(N.LAST_UPDATE_BY)

FROM NoticeBoard N

CROSS JOIN plEmployee E

WHERE E.EP_ENABLE_WEBLEAVE=1

AND ([EFFECTIVE_FROM] IS NULL OR [EFFECTIVE_FROM]<=dbo.Today()) 

AND ([EFFECTIVE_TO] IS NULL OR [EFFECTIVE_TO]>=dbo.Today())

AND (

      dbo.IsNoticeBoardAdmin(E.EE_NO)=1 

OR (N.DEPT_CODE='' AND N.DIVISION_CODE='' AND N.WORK_LOCATION='')

OR (N.DEPT_CODE>'' AND N.DEPT_CODE=E.DEPT_CODE)

OR (N.DIVISION_CODE>'' AND N.DIVISION_CODE=E.DIVISION_CODE)

OR (N.WORK_LOCATION>'' AND N.WORK_LOCATION=E.WORK_LOCATION)

       )

   AND CAST(dbo.ReadPersonalOption(E.EE_NO,'NoticeBoard',NB_ID,'False') AS BIT)=0

ORDER BY E.EMAIL, N.NB_DATE

SELECT 

EMAIL = E.EMAIL,

EE_NAME = A.[NAME],

[APPLICATION_TYPE] = [APPLICATION],

[LEAVE_TYPE] = dbo.LeaveTypeDescription(A.[LEAVE_CODE]),

[APPLICATION_DESCRIPTION] = [LeaveApplyDescBy],

[DAYS/HOURS/AMOUNT] = [DAYS_HOURS],

[APPLY_DATE] = dbo.fnFormatDateTime(A.CREATE_DATE),

A.[STATUS]

FROM v_WebLeaveApply A

INNER JOIN v_Employee E ON E.EE_NO=A.EE_NO

WHERE [STATUS]='Approved' AND 

[APPLICATION]='Leave Application' AND

dbo.IsRequiredButMissingAttachment('L',RowSerial)=1 AND

dbo.IsPendingCancelRequestExists(RowSerial)=0

ORDER BY E.EMAIL,A.CREATE_DATE

SELECT  [APPRAISEE_EMAIL]=dbo.EEEmail([EE_NO]),

[Appraisal No.]=[APPRAISAL_NO],

[Employee]=dbo.EENameNo(EE_NO),

[Release Date]=[RELEASED_DATE],

[Deadline]=[APPRAISAL_DEADLINE],

[Status]=dbo.AppraisalStatus([APPRAISAL_NO])

FROM paAppraisalMaster

WHERE dbo.AppraisalStatusCode(APPRAISAL_NO)='IN_SELF_REVIEW' 

ORDER BY dbo.EEEmail([EE_NO]),[APPRAISAL_NO]

-- %NOFOOTER%

SELECT 

EMAIL, 

[Employee No.]=EE_NO,

[Last Year Annual Leave Balance]=dbo.AL_BroughtForwardRemaining(EE_NO,DATEADD(DAY,dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0),dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0)),0),

[Retain days allowed]=dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0),

[Date to Forfeiture]=DATEADD(DAY,dbo.AutoAnnualLeaveForfeitureGracePeriodDays(EE_NO,dbo.Today(),0),dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0)),

[Forfeiture Days]=(dbo.AL_BroughtForwardRemaining(EE_NO,DATEADD(DAY,dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0),dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0)),0))-dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0)

FROM plEmployee L

WHERE dbo.IsActiveEmployeeOnForLeave(L.EE_NO,dbo.Today())=1

AND dbo.IsAutoAnnualLeaveForfeiture(L.EE_NO,dbo.Today(),0)=1

AND (dbo.AL_BroughtForwardRemaining(EE_NO,DATEADD(DAY,dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0),dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0)),0))-dbo.AutoAnnualLeaveForfeitureRetainDays(EE_NO,dbo.Today(),0)>0

AND dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0)<=dbo.Today()

AND DATEADD(DAY,dbo.AutoAnnualLeaveForfeitureGracePeriodDays(EE_NO,dbo.Today(),0),dbo.LastEntitlementDate(EE_NO,dbo.Today(),'',0))>=dbo.Today()

SELECT E.EMAIL,

       [Employee No.] = A.EE_NO,

       [Description] = 'You forget to punch in today'

FROM v_PLFAS A

INNER JOIN plEmployee E ON A.EE_NO=E.EE_NO

WHERE A.HR_DATE=dbo.Today()

AND A.CARD_IN_TIME1=''

AND dbo.TimeDiff(dbo.RegularWorkHoursInTime(A.EE_NO,A.HR_DATE),dbo.CurrentTime())>=3

AND E.EMAIL>''

AND dbo.IsEELeave(A.HR_DATE,A.EE_NO)=0

SELECT E.EMAIL,

       [Employee No.] = A.EE_NO,

       [Description] = 'You forget to punch out today'

FROM v_PLFAS A

INNER JOIN plEmployee E ON A.EE_NO=E.EE_NO

WHERE A.HR_DATE=dbo.Today()

AND A.CARD_IN_TIME1=''

AND dbo.TimeDiff(dbo.RegularWorkHoursOutTime(A.EE_NO,A.HR_DATE),dbo.CurrentTime())>=3

AND E.EMAIL>''

AND dbo.IsEELeave(A.HR_DATE,A.EE_NO)=0

SELECT * FROM v_IC_DuplicateAppointmentHistoryStartDateCheck

SELECT * FROM v_IC_EEJoinDateAppointmentStartDateCheck

SELECT * FROM v_IC_WorkHrsRosterCodeIntegrityCheck

SELECT * FROM v_IC_JoinDatevsMPFDate

SELECT * FROM v_IC_LeaveDateVsJoinDateTermnDate

SELECT * FROM v_IC_plLeave_plLeaveDayRecord

SELECT * FROM v_IC_PayrollPeriodVsJoinDateTermnDate

SELECT * FROM v_IC_SalaryPositionIntegrityCheck

SELECT * FROM v_IC_WorkPayVsBasicSal

SELECT * FROM v_IC_DuplicatedEMAIL

SELECT * FROM v_IC_AttendanceDateVsJoinDateTermnDate

SELECT * FROM v_IC_InvalidCostCenterPercentageSum

SELECT * FROM v_IC_LeaveEntryDays_CalculatedDays

SELECT [DESCRIPTION]=dbo.plReminderDescription(1,'en-US')+': '+[PERIOD] 

FROM [plPayrollBatch] P

WHERE P.POSTED=0 

SELECT [DESCRIPTION] = dbo.plReminderDescription(2,'en-US')+' :'+dbo.HRPeriod(dbo.EOLM(dbo.Today()))

WHERE NOT EXISTS(SELECT 1 FROM [plPayrollBatch] P WHERE P.[PERIOD]=dbo.HRPeriod(dbo.EOLM(dbo.Today())))

UNION ALL 

SELECT [DESCRIPTION] = dbo.plReminderDescription(2,'en-US')+' :'+dbo.HRPeriod(dbo.Today())

WHERE NOT EXISTS(SELECT 1 FROM [plPayrollBatch] P WHERE P.[PERIOD]=dbo.HRPeriod(dbo.Today()))

SELECT 

[DESCRIPTION] = 'New Employee(s) of these2 Month(s)',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Join Date: '+dbo.fnFormatDate(E.JOIN_DATE)

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),E.JOIN_DATE)>=-2

ORDER BY E.JOIN_DATE

SELECT 

[DESCRIPTION] = 'Terminated Employee(s) of these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Termination Date: '+dbo.fnFormatDate(E.TERMN_DATE)

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),E.TERMN_DATE)>=-1

ORDER BY E.TERMN_DATE

SELECT 

[DESCRIPTION] = 'MPF Enrollment Dead Line Employee(s) of these 2 Month(s)',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Dead Line: '+dbo.fnFormatDate(dbo.MPFEnrollmentDeadLine(E.EE_NO))

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),dbo.MPFEnrollmentDeadLine(E.EE_NO))>=-2

AND E.PFUND_TYPE='MPF'

ORDER BY dbo.MPFEnrollmentDeadLine(E.EE_NO)

SELECT 

[DESCRIPTION] = 'Probation End Employee(s) of these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Probation End Date: '+dbo.fnFormatDate(E.PROBATION_END_DATE)

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),PROBATION_END_DATE)>=-2

ORDER BY PROBATION_END_DATE

SELECT 

[DESCRIPTION] = 'Contract End Employee(s) of these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Contract End Date: '+dbo.fnFormatDate(E.CONTRACT_END_DATE)

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),CONTRACT_END_DATE)>=-2

ORDER BY CONTRACT_END_DATE

SELECT 

[DESCRIPTION] = 'On Birthday Employee(s) of these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Date of Birth: '+dbo.fnFormatDate(E.BIRTHDATE)+'  '+'Age: '+CONVERT(NCHAR(3),dbo.CalcCompleteYears(BIRTHDATE,dbo.NextAnniversaryDate(E.BIRTHDATE,dbo.EOLM(dbo.Today()))))

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),dbo.NextAnniversaryDate(BIRTHDATE,dbo.EOLM(dbo.Today())))<2

ORDER BY dbo.NextAnniversaryDate(BIRTHDATE,dbo.EOLM(dbo.Today()))

SELECT 

[DESCRIPTION] = 'Work Anniversary within these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+'Anniversary: '+dbo.fnFormatDate(dbo.NextAnniversaryDate(E.JOIN_DATE,dbo.EOLM(dbo.Today())))+'  '+'Yrs of Service: '+dbo.PadLeft(CONVERT(NCHAR(3),dbo.ServicingYrs(E.EE_NO, dbo.NextAnniversaryDate(JOIN_DATE,dbo.EOLM(dbo.Today())))),2)

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),dbo.BOM(dbo.NextAnniversaryDate(JOIN_DATE,dbo.EOLM(dbo.Today()))))<2

ORDER BY dbo.NextAnniversaryDate(JOIN_DATE,dbo.EOLM(dbo.Today()))

SELECT [DESCRIPTION] = 'IR56B Batch not yet Protected',

[DETAIL_LINE] = CONVERT(NCHAR(4),[TAX_YEAR])+'-'+CONVERT(NCHAR(4),[TAX_YEAR]+1)+IIF(EMPLOYER_CODE='','',' ('+EMPLOYER_CODE+')')

FROM plIR56B2Batch P

WHERE P.POSTED=0 

SELECT [DESCRIPTION] = 'IR56E not yet printed',

[DETAIL_LINE] = E.[EE_NO]+' '+'Join Date: '+dbo.fnFormatDate(JOIN_DATE)

FROM v_Employee E  

WHERE dbo.IsActiveEmployeeOn(E.EE_NO,dbo.Today())=1

AND E.HK_INCOME_TAX=1

AND E.IS_NON_EMPLOYEE_IR56M=0

AND E.PRINT_IR56E<>'Y'

ORDER BY E.JOIN_DATE

SELECT [DESCRIPTION] = 'IR56F/G not yet printed',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+' '+'Termination Date: '+dbo.fnFormatDate(E.TERMN_DATE)

FROM v_Employee E  

WHERE dbo.IsCurrentUserManagedEE(E.EE_NO)=1

AND E.HK_INCOME_TAX=1

AND IS_NON_EMPLOYEE_IR56M=0

AND E.PRINT_IR56FG<>'Y'

AND E.TERMN_DATE IS NOT NULL

AND EXISTS(SELECT 1 FROM plPayroll P WHERE P.EE_NO=E.EE_NO)

ORDER BY E.TERMN_DATE

SELECT [DESCRIPTION] = 'Age Reach 65 Employee(s) of these 2 months',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+' '+' '+'Date of Birth: '+dbo.fnFormatDate(BIRTHDATE)+'  '+'Age: '+CONVERT(NCHAR(3),dbo.CalcCompleteYears(BIRTHDATE,dbo.NextAnniversaryDate(BIRTHDATE,dbo.EOLM(dbo.Today()))))

FROM v_Employee E  

WHERE DATEDIFF(MONTH,dbo.Today(),DATEADD(YEAR,65,BIRTHDATE))>0

    AND DATEDIFF(MONTH,dbo.Today(),DATEADD(YEAR,65,BIRTHDATE))<2

AND dbo.IsActiveEmployeeOn(E.EE_NO,dbo.Today())=1

ORDER BY dbo.NextAnniversaryDate(BIRTHDATE,dbo.Today())

SELECT [DESCRIPTION] = 'Group Anniversary within these 2 months (Employees Group Anniversary)',

[DETAIL_LINE] = E.EE_NAME+' ['+E.[EE_NO]+'] '+' '+'Group Anniversary: '+dbo.fnFormatDate(dbo.NextAnniversaryDate(dbo.GroupJoinDate(E.EE_NO),dbo.EOLM(dbo.Today())))+'  '

+'Yrs of Service: '+dbo.PadLeft(CONVERT(NCHAR(3),dbo.ServicingYrs(E.EE_NO, dbo.NextAnniversaryDate(dbo.GroupJoinDate(E.EE_NO),dbo.EOLM(dbo.Today())))),2)

FROM v_Employee E  

WHERE

DATEDIFF(MONTH,dbo.Today(),dbo.BOM(dbo.NextAnniversaryDate(dbo.GroupJoinDate(E.EE_NO),dbo.EOLM(dbo.Today()))))=1

AND dbo.IsCurrentUserManagedEE(E.EE_NO)=1

AND dbo.IsActiveEmployeeOn(E.EE_NO,dbo.Today())=1

ORDER BY dbo.NextAnniversaryDate(dbo.GroupJoinDate(E.EE_NO),dbo.EOLM(dbo.Today()))

SELECT [DESCRIPTION] = 'IR56M Batch not yet Protected',

[DETAIL_LINE] = CONVERT(NCHAR(4),[TAX_YEAR])+'-'+CONVERT(NCHAR(4),[TAX_YEAR]+1)+IIF(EMPLOYER_CODE='','',' ('+EMPLOYER_CODE+')')

FROM plIR56MBatch P

WHERE P.POSTED=0 

SELECT [DESCRIPTION] = 'IR56F Batch not yet Protected',

[DETAIL_LINE] = CONVERT(NCHAR(4),[TAX_YEAR])+'-'+CONVERT(NCHAR(4),[TAX_YEAR]+1)+IIF(EMPLOYER_CODE='','',' ('+EMPLOYER_CODE+')')

FROM plIR56F2Batch P

WHERE P.POSTED=0 

SELECT [REMINDER_DESCRIPTION] = 'Meet Employee User Defined Field 11',

E.[EE_NO],

S.USER11_NAME,

USER11_DATE = dbo.fnFormatDate(E.USER11)

FROM plEmployee E  

INNER JOIN plSetup S ON S.COMPANY_CODE='00'

AND E.USER11 IS NOT NULL

AND dbo.WithinXMonths(E.USER11,2)=1 

ORDER BY E.USER11

SELECT [REMINDER_DESCRIPTION] = 'Meet Company User Defined Field 16',

G.COMPANY_USER16_NAME,

USER11_DATE = dbo.fnFormatDate(G.COMPANY_USER16_VALUE)

FROM GBPARM G

WHERE G.COMPANY_USER16_VALUE IS NOT NULL

AND dbo.WithinXMonths(G.COMPANY_USER16_VALUE,2)=1 

ORDER BY G.COMPANY_USER16_VALUE

SELECT [DESCRIPTION] =  'Reach the Retirement Age within these 2 months',

  E.[EE_NO],

  [Date of Birth] = dbo.fnFormatDate(BIRTHDATE),

  [Age] = CONVERT(NCHAR(3),dbo.CalcCompleteYears(BIRTHDATE,dbo.NextAnniversaryDate(BIRTHDATE,dbo.EOLM(dbo.Today())))),

  [Retirement Age] = dbo.GNF(E.RETIREMENT_AGE),

  [Retirement Date] = dbo.fnFormatDate(DATEADD(YEAR,E.RETIREMENT_AGE,BIRTHDATE))

  FROM v_Employee E  

  WHERE dbo.WithinXMonths(DATEADD(YEAR,E.RETIREMENT_AGE,BIRTHDATE),2)=1

  ORDER BY DATEADD(YEAR,E.RETIREMENT_AGE,BIRTHDATE)