CREATE VIEW dbo.PW001SRV113
as
SELECT
P03.CrewId as 'Safety ID',
P03.CabinID as 'Cabin',
p01.pin as Pin,
vsl.name  as Ship,
DEP.NAME as Department,
POS.NAME as Position,
p01.Lastname as 'Last Name',
p01.Firstname 'First Name',
p01.Name as 'Name',
p01.Nationality,
COUNTRY.COUNTRYNAME AS 'Nationality Name',
p01.Sex as 'Gender',
case when p01.sex='F' then 'Female'
when p01.sex='M' then 'Male' end as 'Gender Name',
p01.Birthdate as 'DOB',
p01.PLACEOFBIRTH as 'Place of Birth',
P1P.TDNUMBER as 'Passport Number',
P1P.DATEFROM as 'Passport Issue Date',
P1P.DATETO as 'Passport Expiry Date',
C1D.TDNUMBER as 'C1D Number',
C1D.DATEFROM as 'C1D Issue Date',
C1D.DATETO as 'C1D Expiry Date',
SBOOK.TDNUMBER as 'SB Number',
SBOOK.DATEFROM as 'SB Issue Date',
SBOOK.DATETO as 'SB Expiry Date',
dbo.ad_SignOnDate(p03.NUMORGID, p01.PIN, P03.DATEFROM) as 'Sign On Date',
ISNULL(p03.DATETO, P03.TODATEESTIMATED) as 'Sign Off Date',
p01.Client numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate

FROM dbo.pw001p01 p01
LEFT JOIN PWCOUNTRY COUNTRY ON P01.NATIONALITY=COUNTRY.COUNTRYCODE
JOIN dbo.PW001P03 P03 ON P01.PIN = P03.PIN
AND P03.CODE in (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%') AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
AND P03.PLANNED <> 'Y'
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 PW001P08 P1P ON P01.PIN=P1P.PIN AND P1P.CODE='PP'
LEFT JOIN PW001P08 C1D ON C1D.PIN=P01.PIN AND C1D.CODE='VISAC1D'
LEFT JOIN PW001P08 SBOOK ON SBOOK.PIN=P01.PIN AND SBOOK.CODE='SBOTH'