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

Summary	Shows all the crew with current sick activity
Keywords	Activities, Sick Period
Category	Crew List View

Description

The view shows all the crew with current sick activity.

View Sample

SQL statement

CREATE VIEW dbo.VIEWID
AS

SELECT p01.pin,
       p01.NAME,
       p01.LASTNAME,
       p01.FIRSTNAME,
       p01.NATIONALITY,
       ORG.NAME                     ORGANIZATION,
       p01.Client                   numorgid,
       p01.Sex                      GENDER,
       p01.BIRTHDATE,
       birthcountry.COUNTRYNAME  AS 'BIRTH COUNTRY',
       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.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
            AND EXISTS(
                    SELECT CODE
                    FROM   PW001C12 c12
                    WHERE  c12.CODE = p03.CODE
                           AND c12.OPTIONS LIKE '%K%'
                )
            AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
            AND P03.PLANNED <> 'Y'
       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

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 > Estimated End Date
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 - Current

Description

View Sample

SQL statement

Columns Specification