CREATE VIEW dbo.PW001SRV230
AS
SELECT
       P01.PIN,
       P01.NAME,
       P01.LASTNAME,
       P01.FIRSTNAME,
       P01.SEX,
       COUNTRY.COUNTRYNAME AS 'Nationality',
       orgves.NAME AS Vessel,
       P01.BIRTHDATE,
       P08.TDNUMBER AS PASSPORT,
       P08.DATETO AS 'PASSPORT EXPIRY DATE',
       P081.TDNUMBER AS SBOOK,
       P081.DATETO AS 'SBOOK EXPIRY DATE',
       p01.contractstartdate AS "Contract_StartDate",
       p01.Contractexpirydate AS "Contract_ExpiryDate",
       c02.NAME AS 'Current rank',
       pas2.DATEFROM AS 'Date of position change',
       c02prom.Name AS 'Position after the position change',
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       P01.CLIENT NUMORGID
FROM PW001P01 AS P01
     JOIN PWROT_ACTIVE_SHIFT AS pas
          ON pas.PIN = P01.PIN
         AND pas.DATEFROM <= CONVERT(VARCHAR, GETDATE(), 112)
         AND pas.DATETO >= CONVERT(VARCHAR, GETDATE(), 112)
     JOIN PWROT_ACTIVE_SHIFT AS pas2
          ON pas.PROMOTED_TO = pas2.SEQUENCENO
     LEFT JOIN PW001C02 AS c02
          ON c02.CODE = pas.POSITION
     LEFT JOIN PW001C02 AS c02prom
          ON c02prom.CODE = pas2.POSITION
     LEFT JOIN PW001P08 AS P08
          ON P08.PIN = P01.PIN
         AND P08.CODE = 'PASS'
     LEFT JOIN PW001P08 AS P081
          ON P081.PIN = P01.PIN
         AND P081.CODE = 'SBOOK'
     LEFT JOIN PWCOUNTRY AS COUNTRY
          ON COUNTRY.COUNTRYCODE = P01.NATIONALITY
JOIN PW001P03 p03
            ON  p03.PIN = p01.PIN
       AND p03.PLANNED = 'N'
            AND p03.DATEFROM <= GETDATE()
            AND ISNULL(p03.DATETO, GETDATE()) >= GETDATE()    
LEFT JOIN PWORG orgves
            ON  orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)