CREATE VIEW dbo.PW001SRV43
as
SELECT
p01.pin as Pin,
p01.Name as 'Name',
p01.Firstname 'First Name',
p01.MIDDLENAME 'Middle Name',
p01.Lastname as 'Last 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',
vsl.name  as Ship,
vsl.NUMORGID as Vessel_numorgid,
DEP.NAME as Department,
POS.NAME as Position,
--P03.CabinID as 'Cabin',
dbo.ad_SignOnDate(p03.NUMORGID, p01.PIN, P03.DATEFROM) as 'Sign On Date',
ISNULL(p03.DATETO, P03.TODATEESTIMATED) as 'Sign Off Date',
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',
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='SB'