CREATE VIEW dbo.PW001SRV50PW001SRV134 AS 

SELECT p01.pin               AS selectPIN,
       p01.pin asP01.ALTERNATIVEPIN    AS 'ALTERNATIVE PIN',
	       CASE 
           case whenWHEN p01.sex = 'M' thenTHEN 'Mr'
	            whenWHEN p01.sex = 'F' thenTHEN 'Mrs'
       END    end as               AS Gender,
       p01.NAME as              AS 'Full Name',
       p01.FIRSTNAME as         AS 'First Name',
       p01.MIDDLENAME as        AS 'Middle Name',
       p01.LASTNAME as          AS 'Last Name',
       dateaddDATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate) 'Celebration Date',
       MonthMONTH(p01.birthdate) as AS 'Month Nr',
,       DateName(mm,DateaddDATENAME(
           mm,
           DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), p01.birthdate)
       )  Month,                      MONTH,
       CONVERT(
           INT,
           DATENAME(
               d,
               DATEADD(YY, DATEDIFF(YY, P01.BIRTHDATE, GETDATE()), BIRTHDATE)
           )
       )                     AS DateDATE,
       DateName(wk,DateaddDATENAME(
           wk,
           DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate)
       )                        Week,
       DateName(weekday,DateaddDATENAME(
           weekday,
           DATEADD(yy,datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()), birthdate)
       )  Day,                        DAY,
       DatediffDATEDIFF(yy, p01.birthdate,getdate GETDATE()) Age,
       p01.Birthdate,
     	  p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME asAS 'Country / Location',
       PCAB.Cabin,
       POS.NAME as              AS Position,
     	  P0T.TELENO as            AS Email,
	       mob.Teleno as            AS 'Mobile Phone',
       p01.RANK as              AS 'Current Rank',
   	    p01.Client               numorgid,
       VSL.NUMORGID          AS vessel_numorgid,
       p01.EmploymentStartDate,
       p01.EmploymentEnddate,
  from dbo.pw001p01 p01  JOIN dbop01.PW001P03COSTPLACE P03            AS 'Department/Cost Place Code',
       c43.[NAME]  ON P01.PIN = P03.PIN           AS 'Department/Cost Place'
FROM   dbo.pw001p01 p01
       JOIN dbo.PW001P03 AND (P03.DATETO
IS NULL or P03.DATETO >= GETDATE())       ON  P01.PIN = P03.PIN
            AND (P03.DATETO IS NULL OR P03.DATETO    >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
LEFt
       LEFT JOIN Pw001C12 act on
            ON  act.code = p03.code
       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     LEFT JOIN dbo.PWORGVESCAB PCAB
            ON  PCAB.CABINID = p03.cabinid
       LEFT JOIN PWCOUNTRY COUNTRY
            ON  P01.COUNTRYOFBIRTH  = COUNTRY.COUNTRYCODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001P0T P0T
            ON  (
                    (P01.PIN = P0T.PIN)
                    AND (P0T.TELETYPE = 6)
                    AND (NOT P0T.TELEPRIORITY IS NULL)
                    AND NOT EXISTS
                        (
                            SELECT SEQUENCENO
                            FROM   PW001P0T P0T2
                            WHERE  (P0T.PIN = P0T2.PIN)
                                   AND (P0T2.TELETYPE = 6)
                                   AND (
                                           (P0T2.TELEPRIORITY < P0T.TELEPRIORITY)
                                           OR (
                                                  (P0T2.TELEPRIORITY = P0T.TELEPRIORITY)
                                                  AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)
                                              )
                                       )
                        )
                )
       LEFT JOIN PW001P0T mob
            ON  (
                    (P01.PIN = mob.PIN)
                    AND (mob.TELETYPE = 3)
                    AND (NOT mob.TELEPRIORITY IS NULL)
                    AND NOT EXISTS
                        (
                            SELECT SEQUENCENO
                            FROM   PW001P0T mob2
                            WHERE  (mob.PIN = mob2.PIN)
                                   AND (mob2.TELETYPE = 3)
                                   AND (
                                           (mob2.TELEPRIORITY < mob.TELEPRIORITY)
                                           OR (
                                                  (mob2.TELEPRIORITY = mob.TELEPRIORITY)
                                                  AND (mob2.SEQUENCENO < mob.SEQUENCENO)
                                              )
                                       )
                        )
                )
WHERE where act.OPTIONS like              LIKE '%S%'