1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification

Summary	Shows all the crew members historical sick activities
Keywords	Activities
Category	Crew List View

Description

Shows all the crew members historical sick activities

View Sample

Main Data Selection

All crew members with historical sick activity

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
Organization	Personal Details > Employment > Organization/Company
Gender	Personal Details > Personal > Gender
Birthdate	Personal Details > Personal > Birthdate
Birth Country	Personal Details > Personal > Birth country
Rank	Datagroups > Activities > Rank
Sort	Rank code > Groupno
Activity	Activity code
Activity Start	Datagroups > Activities > Date From
Activity End	Datagroups > Activities > Date To/Estimated End Date
Days	Datagroups > Activities > Days
Vessel	Datagroups > Activities > Vessel name
Department	Datagroups > Activities > Department name
Contract Start	Personal Details > Employment > Contract Start Date
Contract End	Personal Details > Employment > Contract End Date

Adonis HR Reports, Views and Similar

Historical: Crew on Sick

Description

View Sample

Main Data Selection

SQL statement

Columns Specification

Related content