Summary	Standard view that shows basic information about the crew members.
Keywords	Basic info
Category	Crew List View

Description

Standard view that shows basic information about all the crew members/employees.

View Sample

Main Data Selection

All the persons.

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.

CREATE VIEW dbo.PW001SRV12
AS

SELECT P01.PIN                               PIN,
       CASE 
            WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
            WHEN p0u.AVPLAN IS NOT NULL THEN 'Available Planning'
            WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NOT NULL THEN 'Available to Return'
            WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NULL THEN 'Available'
       END                                AS [STATUS],
       P01.ALTERNATIVEPIN                 AS 'ALTERNATIVE PIN',
       P01.NAME                           AS 'Full Name',
       P01.TITLENAME                      AS 'Title Name',
       P01.FIRSTNAME                      AS 'First Name',
       P01.MIDDLENAME                     AS 'Middle Name',
       P01.LASTNAME                       AS 'Last Name',
       P01.MAIDENNAME                     AS 'Maiden Name',
       CASE 
            WHEN p01.SEX = 'M' THEN 'Male'
            WHEN p01.SEX = 'F' THEN 'Female'
            ELSE 'Undefined'
       END                                AS Gender,
       c04.[TEXT]                         AS 'Marital Status',
       nat.NATIONALITY                    AS Nationality,
       ORG.NAME                           AS 'Organization',
       P01.CLIENT                            Numorgid,
       P01.EmploymentStartDate,
       P01.EmploymentEndDate,
       c55.NAME                           AS 'Termination Reason',
       p01Rank.[NAME]                     AS 'Current Rank',
       p01.COSTPLACE                      AS 'Department/Cost Place Code',
       c43.[NAME]                         AS 'Department/Cost Place',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.ADDRESS3                       AS 'City',
       P01.POSTCODE 'Post Code',
       P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME                     AS 'Address Country',
       P01.HOMEAIRPORT 'Home Airport',
       email.TELENO                       AS 'E-MAIL',
       mob.TELENO                         AS 'Mobile Phone',
       P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO                   AS 'Personal ID',
       PASS.TDNUMBER 'Passport No',
       PASS.DATETO                        AS 'Passport Expiry',
       passc.COUNTRYNAME                  AS 'Passport Country of Issue',
       CASE 
            WHEN CONTRACTKIND = 0 THEN 'Main Contract'
            WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
            WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE ''
       END                                AS 'Contract Type',
       C32CONT.Text 'Contract Name',
       C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
       p20.DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
            WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
       END                                AS 'Contract Scan Status'
FROM   PW001P01 P01
       LEFT JOIN (
                SELECT P8.PIN,
                       P8.tdnumber,
                       P8.datefrom,
                       P8.dateto,
                       p8.ISSUEDWHERE,
                       p8.nativename,
                       p8.ISSUE_COUNTRY,
                       p8.PASSPORTPRIORITY
                FROM   dbo.PW001P08 P8
                       JOIN dbo.PW001C23 C23
                            ON  P8.CODE = C23.CODE
                            AND C23.OPTIONS LIKE '%T%'
                WHERE  NOT EXISTS (
                           SELECT 1
                           FROM   PW001P08 t
                                  JOIN dbo.PW001C23 C23
                                       ON  t.CODE = C23.CODE
                                       AND C23.OPTIONS LIKE '%T%'
                           WHERE  t.PIN = p8.PIN
                                  AND (
                                          t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
                                          OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
                                          AND t.SEQUENCENO < p8.SEQUENCENO
                                      )
                       )
            ) pass
            ON  pass.PIN = P01.PIN
       LEFT JOIN PWCOUNTRY passc
            ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY
       LEFT JOIN PW001P0P P0P
            ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001C02 p01Rank
            ON  p01Rank.CODE = p01.[RANK]
       LEFT JOIN Pw001C33 c33
            ON  c33.code = p01.CATHEGORYA
       LEFT JOIN PW001C02 C02
            ON  P0P.POSITIONID = C02.CODE
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       LEFT JOIN PWCOUNTRY PC
            ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
       LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
       LEFT JOIN PWORG PV
            ON  P01.PAYROLLVSL = PV.NUMORGID
       LEFT JOIN PW001P20 p20
            ON  p20.PIN = p01.PIN
            AND p20.HISTORICAL = 'F'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P20 t
                    WHERE  t.PIN = p20.PIN
                           AND t.HISTORICAL = 'F'
                           AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001OLEDOCS sdoc
            ON  sdoc.DOCNO = p20.SCANNEDDOCNO
       LEFT JOIN PW001C02 C02CONT
            ON  p20.RANK = C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
            ON  p20.CONTRACTTYPE = C32CONT.code
       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
                               )
                )
       LEFT JOIN PW001P0T mob
            ON  mob.PIN = p01.PIN
            AND mob.TELETYPE = 3
            AND mob.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0T t
                    WHERE  t.PIN = mob.PIN
                           AND t.TELETYPE = mob.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
                           AND (
                                   t.TELEPRIORITY < mob.TELEPRIORITY
                                   OR t.TELEPRIORITY = mob.TELEPRIORITY
                                   AND t.SEQUENCENO < mob.SEQUENCENO
                               )
                )
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001P0U p0u
            ON  p0u.PIN = p01.PIN
       LEFT JOIN PW001C55 c55
            ON  p01.STOPREASON = c55.CODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001C04 c04
            ON  c04.CODE = p01.MARITALSTATUS
       LEFT JOIN dbo.PW001P03 P03cur
            ON  P01.PIN = P03cur.PIN
            AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
            AND P03cur.DATEFROM <= GETDATE()
            AND P03cur.PLANNED <> 'Y'
       LEFT JOIN PW001C12 c12
            ON  c12.CODE = p03cur.CODE

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
Alternative PIN	Personal Details > Personal > Alternative PIN
Full Name	Personal Details > Personal > Full Name
Title Name	Personal Details > Personal > Title Name
First Name	Personal Details > Personal > First Name
Middle Name	Personal Details > Personal > Middle Name
Last Name	Personal Details > Personal > Last Name
Maiden Name	Personal Details > Personal > Maiden Name
Gender	Personal Details > Personal > Gender
Organization	Personal Details > Employment > Organization’s Name
Current Rank	Personal Details > Employment > Current Rank
Numorgid	Personal Details > Employment > Organization’s No
EmploymentStartDate	Personal Details > Employment > Employment Start Date
EmploymentEndDate	Personal Details > Employment > Employment End Date
Termination Reason	Personal Details > Employment > Termination Reason
Nationality	Personal Details > Personal > Nationality
Birth date	Personal Details > Personal > Birth date
Personal ID	Personal Details > Personal > Personal ID
Passport No	Travel Documents > Current Passport No
Passport Expiry	Travel Documents > Current Passport Date
Passport Country of Issue	Travel Documents > Current Passport Issue Country
Address	Personal Details > Personal > Address
City	Personal Details > Personal > City
Post Code	Personal Details > Personal > Post Code
Post Place	Personal Details > Personal > Post Place (used for City)
Address Country	Personal Details > Personal > Address Country
Home Airport	Personal Details > Personal > Home Airport 1
E-MAIL	Personal Details > Personal > Telecommunication > E-mail with highest priority (e.g. 1 has more priority than 4)
Mobilr Phone	Personal Details > Personal > Telecommunication > Mobile Phone with highest priority (e.g. 1 has more priority than 4)
Marital Status	Personal Details > Personal > Marital Status
Contract Type	Contracts Datagorup > Current contract type: Main, Amendment, Sub.
Contract Name	Contracts Datagorup > Current contract name.
Contract Rank	Contracts Datagorup > Current contract rank.
Contract Start	Contracts Datagorup > Current contract Start Date.
Contract End	Contracts Datagorup > Current contract End Date.
Trial period duration	Contracts Datagorup > Current contract trial period duration.
Trial period end	Contracts Datagorup > Current contract trial period end.
Contract Pasycale Table	Contracts Datagorup > Current contract payscale table.
Contract Pasycale Code	Contracts Datagorup > Current contract payscale code.
Contract Scan Status	Contracts Datagorup > Current contract scan status.
Emplyee Portal Access From	Personal Details > Personal > Customised Fields > Emplyee Portal Access From
STATUS	The current Activity Name should be set as a status. If no current activity + if the crew doesn’t have any sea-service activities records of any status + if the person has a date added in the customized field “Available for Planning from“ in Personal Details, then Available Planning status should be used. If the crew doesn’t have any current activity, and if the crew is already planned to haver some activity, then the status should be Planned to “Nearest Planned Activity Name“ + “Nearest Planned Date From“. If no current activity + if the crew doesn’t have any sea-service activities records of any status + if no planned activities, then Available Planning status should be used. If no current activity + if the Available Planning is not applicable + if the person has a date added in the customized field “Available to Return“, then the Available to Return status should be used. If no current activity + if the Available Planning is not applicable + Available to Return is not applicable, then the Available status should be used.

Start Page

N/A

Reports, Views and Similar

Standard View Extended (all crew)