Versions Compared

Version Old Version 3 New Version 4
Changes made by

Daria Chebotaieva

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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

Code Block
languagesql
CREATE VIEW dbo.VIEWIDPW001SRV279 
as

select 
       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      P0U.EP as 'E-mail send out', 
              email.TELENOTELENO   as 'E-mail address'
from PW001P01 P01
--left join pw001u02PW001P0U u02P0U on u02P0U.pin = p01.pin
LEFT JOIN PW001P0T email
            ON              ON  email.PIN = p01.PIN
                        AND email.TELETYPE = 6
                       AND email.TELEPRIORITY IS NOT NULL
                        AND NOT EXISTS (
                                        SELECT 1
                    FROM                      FROM   PW001P0T t
                    WHERE                      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 (u02P0U.UField1EP IS NOT NULL or exists (select ac.PIN from WEB_CP_accountACCOUNT_EMPLOYEE AC where P01.PIN=AC.PIN ))

View Sample

...

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