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

Summary	Shows all the crew members who are currently on sick leave
Keywords	Activities
Category	Crew List View

Description

Shows all the crew members who are currently on sick leave.

View Sample

Main Data Selection

All crew members with current sick activity.

SQL statement

CREATE VIEW dbo.PW001SRV44
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
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 Date	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

Current: Crew on Sick

Description

View Sample

Main Data Selection

SQL statement

Columns Specification

Related content