Description
The view gives an overview of all the employees who have or don’t have an account in the Employee Portal.
Selection
VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV20 etc.
It is also possible to show in the view if the “Welcome to Employee Portal” notification e-mail was set to the crew or not. This works only if you setup some customized field in Personal Details. If you need this, contact support for assistance.
CREATE VIEW dbo.VIEWID as
select
P01.PIN,
P01.client as numorgid,
P01.employmentstartdate,
P01.employmentenddate,
P01.Name,
case when exists (select ac.PIN from WEB_CP_ACCOUNT_EMPLOYEE AC where P01.PIN=AC.PIN ) then 'Y'
else 'N'
end 'Account active',
-- u02.UField1 as 'E-mail send out',
email.TELENO as 'E-mail address'
from PW001P01 P01
--left join pw001u02 u02 on u02.pin = p01.pin
LEFT JOIN PW001P0T email
ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM PW001P0T t
WHERE t.PIN = email.PIN
AND t.TELETYPE = email.TELETYPE
AND t.TELEPRIORITY IS NOT NULL
AND (
t.TELEPRIORITY < email.TELEPRIORITY
OR t.TELEPRIORITY = email.TELEPRIORITY
AND t.SEQUENCENO < email.SEQUENCENO
)
)
--where (u02.UField1 IS NOT NULL or exists (select ac.PIN from WEB_CP_account AC where P01.PIN=AC.PIN ))
View Sample
