Business Alert/User Query Samples
Business Alert (BA) provides scheduling ability based on monitoring the HRPro database for critical, time-sensitive information, and sending out alert emails with the most timely data. These queries can also be used in the user query. See also How to execute user query?
The followings are some sample queries for Business Alert/User Query, click the v button to view the query:
1. Holidays in this month
SELECT
[HOLIDAY]=HOLIDAY_DATE,
[DESCRIPTION]=HOLIDAY_DESC
FROM PLHOLIDAY WHERE YEAR(GETDATE())=YEAR(HOLIDAY_DATE) AND MONTH(GETDATE())=MONTH(HOLIDAY_DATE)
2. On Leave Employee(s) Today
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
3. On Leave Employee(s) Next Week
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
4. Last Backup Time
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'
5. Coming Public Holiday (3 days before)
SELECT
[HOLIDAY]=HOLIDAY_DATE,
[DESCRIPTION]=HOLIDAY_DESC
FROM PLHOLIDAY
WHERE REGION='HKG' AND DATEDIFF(d,GETDATE(),HOLIDAY_DATE)=3
6. Employee(s) to be terminated within 3 days
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
7. Employee(s) require appraisal within these 2 weeks
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
8. On Birthday Employee(s) within 2 days
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())
9. Leave Notification for the coming 6 days
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
10. Payroll Change Log (Yesterday)
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
11. Employee Change Log (Yesterday)
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
12. Contract End Employee(s) within these 2 months
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
13. Leave Applications Pending for Approval (email to approvers, for Approval Workflow = By level)
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
14. Pending Applications for Approval of Today (email to approvers, for Approval Workflow = By level)
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]
15. Employee Cost Centres for all Employees
SELECT [EE_NO]
,[START_DATE]
,[COST_CENTRE]
,[PERCENTAGE]
FROM [dbo].[plEECostCentre]
ORDER BY [EE_NO],[START_DATE]
16. Employee Missed Swipe Report (email to employee if there is missed swipe attendance record yesterday)
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
17. Continuous Contract of Employment (418) Checking
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%
18. Appraisals waiting for review (email to reviewers)
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]
19. Appraisals awaiting Appraisee Feedback (email to employees)
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]
20. Appraisals awaiting Feedbacker Feedback (email to Feedbackers)
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]
21. Appraisals waiting for final review (email to reviewers)
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]
22. Appraisals awaiting HR Comment (email to HR)
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]
23. Compensation Leave Balance Expiry Reminder (email to employee for the com leave entitlements will expire within 7 days)
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
24. Leave Type Balance Report (example for Leave Type BL - Birthday Leave)
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
25. Applications Pending for Approval (email to applicant)
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
26. Applications Pending for Approval (email to approvers, for Approval Workflow = Any)
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]
27. Applications Pending for Approval (email to approvers, for Approval Workflow = All)
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]
28. Unread Notification Reminder (email to employees)
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
29. Leave Applications required replenish attachment reminder (email to applicant)
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
30. Appraisals waiting for self review (email to Appraisees)
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]
31. Annual Leave Forfeiture Reminder (email to Employee on new leave year but before grace period reached)
-- %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()
32. Missed Punch-In Reminder (email to employees if they forget to punch in for 3 hours after their standard work in time.)
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
33. Missed Punch-Out Reminder (email to employees if they forget to punch out for 3 hours after their standard work out time.)
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
34. Duplicated Appointment History Start Date Check
SELECT * FROM v_IC_DuplicateAppointmentHistoryStartDateCheck
35. Employee Join Date vs Appointment History Start Date Integrity Check
SELECT * FROM v_IC_EEJoinDateAppointmentStartDateCheck
36. Employees with empty Work Hours Type and Roster Code
SELECT * FROM v_IC_WorkHrsRosterCodeIntegrityCheck
37. Join Date vs MPF Dates Check
SELECT * FROM v_IC_JoinDatevsMPFDate
38. Leave Date vs EE Join Date / Termination Date Integrity Check
SELECT * FROM v_IC_LeaveDateVsJoinDateTermnDate
39. Leave Records vs Leave Day Records
SELECT * FROM v_IC_plLeave_plLeaveDayRecord
40. Payroll Period vs EE Join Date / Termination Date Integrity Check
SELECT * FROM v_IC_PayrollPeriodVsJoinDateTermnDate
41. Salary Info SI vs Appointment History PH Integrity Check
SELECT * FROM v_IC_SalaryPositionIntegrityCheck
42. Work Pay vs Basic Salary
SELECT * FROM v_IC_WorkPayVsBasicSal
43. Duplicated Employee Email Address
SELECT * FROM v_IC_DuplicatedEMAIL
44. Attendance Date vs EE Join Date / Termination Date Integrity Check
SELECT * FROM v_IC_AttendanceDateVsJoinDateTermnDate
45. Invalid Cost Center Percentage Sum Integrity Check
SELECT * FROM v_IC_InvalidCostCenterPercentageSum
46. Leave Entry days vs calculated days
SELECT * FROM v_IC_LeaveEntryDays_CalculatedDays
47. Payroll Batch not yet protected
SELECT [DESCRIPTION]=dbo.plReminderDescription(1,'en-US')+': '+[PERIOD]
FROM [plPayrollBatch] P
WHERE P.POSTED=0
48. Payroll Batch not yet generated
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()))
49. New Employee of these 2 months
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
50. Terminated Employee(s) of these 2 months
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
51. MPF Enrollment Dead Line Employee(s) of these 2 months
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)
52. Probation End Employee(s) of these 2 months
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
53. Contract End Employee(s) of these 2 months
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
54. On Birthday Employee(s) of these 2 months
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()))
55. Work Anniversary within these 2 months
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()))
56. IR56B Batch not yet Protected
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
57. IR56E not yet printed
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
58. IR56F/G not yet printed
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
59. Age Reach 65 Employee(s) of these 2 months
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())
60. Group Anniversary within these 2 months (Employees Group Anniversary)
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()))
61. IR56M Batch not yet Protected
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
62. IR56F Batch not yet Protected
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
63. Meet Employee User Defined Field 11 (to 15) within these 2 months
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
64. Meet Company User Defined Field 16 (to 25) within these 2 months
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
65. Reach the Retirement Age within these 2 months
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)
BA Query Hints
You can put some BA Query Hints at the end of the query to slightly format the result (see example 17).
-- %NoTotal%
-- %NoHeader%
-- %NoFooter%
-- %NoTable%
-- %NoEncode%
-- %FullHeader%
-- %NoBorder%
-- %NoCount%