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