CREATE VIEW dbo.PW001SRV50 AS
select 
      p01.pin as 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.MIDDLENAME as 'Middle Name',
      p01.LASTNAME as 'Last Name',
      dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate) 'Celebration Date',
      Month(p01.birthdate) as 'Month Nr' ,
      DateName(mm,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),p01.birthdate))  Month,
      CONVERT(INT, DATENAME(d,DATEADD(YY,DATEDIFF(YY,P01.BIRTHDATE,GETDATE()),BIRTHDATE))) AS Date,
      DateName(wk,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate))  Week,
      DateName(weekday,Dateadd(yy,datediff(yy,p01.birthdate,getdate()),birthdate))  Day,
      Datediff(yy,p01.birthdate,getdate()) Age,
      p01.Birthdate,
	  p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME as 'Country / Location',
      PCAB.Cabin,
      POS.NAME as Position,
	  P0T.TELENO as Email,
	  mob.Teleno as 'Mobile Phone',
      p01.RANK as 'Current Rank',  
	  p01.Client  numorgid,  
      p01.EmploymentStartDate,
      p01.EmploymentEnddate

from dbo.pw001p01 p01 
JOIN dbo.PW001P03 P03
                        ON P01.PIN = P03.PIN    
                       AND (P03.DATETO IS NULL or P03.DATETO >= GETDATE())                      
                       AND P03.DATEFROM <= GETDATE()
LEFt JOIN Pw001C12 act 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 JOIN dbo.PWORGVESCAB PCAB ON PCAB.CABINID = p03.cabinid
LEFT JOIN PWCOUNTRY COUNTRY ON  P01.COUNTRYOFBIRTH  = COUNTRY.COUNTRYCODE
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%'