Summary	Gives an overview of all the employees who have or don’t have an account in the Employee Portal.
Keywords	APP, Account

Description

The view gives an overview of all the employees who have or don’t have an account in the Employee Portal.

View Sample

Main Data Selection

All the employed persons/crew that have an active account in the Employee Portal.

SQL Statement

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 of whether the “Welcome to Employee Portal” notification e-mail was sent to the crew or not. This works only if you set up some customized field in Personal Details. If you need this, contact support for assistance.

CREATE VIEW dbo.PW001SRV279 
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',
--      P0U.EP as 'E-mail send out', 
       email.TELENO  as 'E-mail address'
from PW001P01 P01
--left join PW001P0U P0U on P0U.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 (P0U.EP IS NOT NULL or exists (select ac.PIN from WEB_CP_ACCOUNT_EMPLOYEE AC where P01.PIN=AC.PIN ))

Columns Specification

Column	Description/ Location in APM
`PIN`	The PIN number from the Personal Details.
`numorgid`	The ID of the person’s Employment Organization.
`employmentstartdate`	The Employment Start Date from the Personal Details.
`employmentenddate`	The Employment End Date from the Personal Details.
`Name`	The Name from Personal Details.
`Account active`	The column show in the person has an active account in the Employee Portal.
`E-mail send out`	The column is disabled in the view. Can be activated, but the customised code value should be related to the one you have in your system. The column shows the date from the customised field in Perosnal Details of when the e-mail was supposed to have been sent out to the crew.
`E-mail address`	The e-mail address from the Personal Details that has highest priority.

Additional Details

N/A

Employee Portal Access