CREATE VIEW dbo.PW001SRV79
as
select.PW001SRV134 AS 

SELECT p01.pin               AS PIN,
       P01.ALTERNATIVEPIN    AS 'ALTERNATIVE PIN',
       CASE 
            WHEN p01.sex = 'M' THEN 'Mr'
            WHEN p01.sex = 'F' THEN 'Mrs'
       END                   AS Gender,
       p01.NAME              AS 'Full Name',
       p01.FIRSTNAME         AS 'First Name',
       p01.pin as PIN,MIDDLENAME        AS 'Middle Name',
       p01.name asLASTNAME          AS 'Last Name',
      dateadd DATEADD(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,Dateadd DATENAME(
           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,
      Datediff DATEDIFF(yy, p01.birthdate,getdate GETDATE()) Age,
       p01.Birthdate,
       p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME AS 'Country / Location',
       PCAB.Cabin,
       POS.NAME as Position, 
	              AS Position,
       P0T.TELENO            AS Email,
       mob.Teleno            AS 'Mobile Phone',
       p01.RANK              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 VSLPWORG 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
where act.OPTIONS like
       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  act.OPTIONS              LIKE '%S%'