selectCREATE VIEW dbo.PW001SRV134 AS 

SELECT p01.pin               AS PIN,
       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 dbo.PW001P03 P03
 p01.COSTPLACE             AS 'Department/Cost Place Code',
       c43.[NAME]                AS 'Department/Cost Place'
FROM   dbo.pw001p01 p01
       JOIN dbo.PW001P03 P03
            ON  P01.PIN = P03.PIN
            AND ISNULL(P03.PLANNED,'N') <> 'Y'
            AND (P03.DATETO IS NULL orOR P03.DATETO >= GETDATE())
            AND P03.DATEFROM <= GETDATE()
       LEFT JOIN Pw001C12 act
            ON  act.code = p03.code
  AND P03.DATEFROM <= GETDATE() LEFt JOIN Pw001C12 act on act.code=p03.code
JOIN PWORG VSL ON 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)
       LEFtLEFT 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%'