1 Description | 2 View Sample | 3 SQL statement | 4 Columns Specification

Summary	Shows all the crew with historical sick activities.
Keywords	Activities, Sick Period
Category	Crew List View

Description

The view shows all the crew with historical sick activities.

View Sample

SQL statement

CREATE VIEW dbo.PW001SRV45
AS

SELECT p01.pin,
       p01.NAME,
       p01.LASTNAME,
       p01.FIRSTNAME,
       p01.NATIONALITY,
       ORG.NAME                     ORGANIZATION,
       p01.Sex                      GENDER,
       p01.BIRTHDATE,
       birthcountry.COUNTRYNAME  AS 'BIRTH COUNTRY',
       P03.Code                  AS 'ACTIVITY',
       p03.DateFrom              AS 'ACTIVITY START',
       ISNULL(p03.DATETO, P03.TODATEESTIMATED) AS 'ACTIVITY END',
       p03.Days                  AS DAYS,
       VSL.NAme                  AS VESSEL,
       DEP.Name                  AS DEPARTMENT,
       p01.contractstartdate     AS 'CONTRACT START',
       p01.Contractexpirydate    AS 'CONTRACT END',
       c02.groupno               AS SORT,
       c02.name                  AS RANK,
       p01.Client                   numorgid,
       p01.EMPLOYMENTSTARTDATE,
       p01.EMPLOYMENTENDDATE
FROM   dbo.pw001p01 p01
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
       LEFT JOIN PWORG VSL
            ON  VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
       LEFT JOIN dbo.PWORG DEP
            ON  DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
       LEFT JOIN dbo.PWORG POS
            ON  POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
       LEFT JOIN dbo.PWORGVESCAB PCAB
            ON  PCAB.CABINID = p03.cabinid
       LEFT JOIN PWCCMCHANGECREWLIST cclj
            ON  cclj.NEXTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT vaj
            ON  vaj.SEQNO = cclj.SEQNO
       LEFT JOIN PWPORT portj
            ON  portj.PORTCODE = vaj.PLACEFROM
       LEFT JOIN PWCCMCHANGECREWLIST ccll
            ON  ccll.CURRENTACT = p03.SEQUENCENO
       LEFT JOIN PWORGVESACT val
            ON  val.SEQNO = ccll.SEQNO
       LEFT JOIN PWPORT portl
            ON  portl.PORTCODE = val.PLACEFROM
       LEFT JOIN PWCOUNTRY birthcountry
            ON  birthcountry.COUNTRYCODE = p01.ADDRESS_COUNTRY
       LEFT JOIN PW001P1P pass
            ON  pass.PIN = p01.PIN
       LEFT JOIN PW001C02 C02
            ON  C02.code = p03.rank
       LEFT JOIN PW001C12 C12
            ON  C12.code = p03.code
WHERE  c12.OPTIONS LIKE '%K%'
       AND p03.DATETO IS NOT NULL
       AND ISNULL(p03.PLANNED, 'N') <> 'Y'

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Personal Details > Personal > PIN
NAME	Personal Details > Personal > Full Name
LASTNAME	Personal Details > Personal > Lastname
FIRSTNAME	Personal Details > Personal > Firstname
NATIONALITY	Personal Details > Personal > Nationality (ISO 3166-1 Alpha-2 code)
ORGANIZATION	Personal Details > Employment > Organization/Company
GENDER	Personal Details > Personal > Gender
BIRTHDATE	Personal Details > Personal > Birthdate
BIRTH COUNTRY	Personal Details > Personal > Birth Country name
ACTIVITY START	Datagroups > Activities > Date From
ACTIVITY END	Datagroups > Activities > Date To
DAYS	Datagroups > Activities > Days
VESSEL	Datagroups > Activities > Vessel name
DEPARTMENT	Datagroups > Activities > Department name
CONTRACT START	Personal Details > Employment > Contract Start
CONTRACT END	Personal Details > Employment > Contract Expiry
SORT	Personal Details > Employment > Rank Sort Number
RANK	Personal Details > Employment > Rank
EMPLOYMENTSTARTDATE	Personal Details > Employment > Start
EMPLOYMENTENDDATE	Personal Details > Employment > End

Reports, Views and Similar

Crew on Sick - Historical

Description

View Sample

SQL statement

Columns Specification