CREATE VIEW dbo.PW001SRV402
AS
SELECT
p01.pin,
p01.Titlename TitleName,
p01.FIRSTNAME FirstName,
p01.middlename MiddleName,
p01.lastname LastName,
p01.Birthdate Birthdate,
p01.sex as Gender,
case when p01.sex='M' then 'him' when p01.sex='F' then 'her' End as Sex,
natcountry.countryname Nationality,
pass.tdnumber PassportNumber,
orgves.NAME AS VesselName,
ves.IMONUMBER as VesselIMONumber,
orgpos.NAME as Position,
p03.datefrom JoiningDate,
(p03.datefrom + 30) as valid,
countryOn.countryname Joining_Country,
'' as CourseDateFrom,
'' as CourseName,
'' as TrainingProvider,
--A bookmark can never be used more than one time, so we need to create the same fields with a different name
--for as many times as needed in the document
p01.Titlename TitleName2,
p01.FIRSTNAME FirstName2,
p01.middlename MiddleName2,
p01.lastname LastName2,
p01.Titlename TitleName3,
p01.FIRSTNAME FirstName3,
p01.middlename MiddleName3,
p01.lastname LastName3,
p01.Titlename TitleName4,
p01.FIRSTNAME FirstName4,
p01.middlename MiddleName4,
p01.lastname LastName4,
orgves.NAME AS VesselName1,
p01.Client as Numorgid,
p01.Employmentstartdate,
p01.Employmentenddate
FROM PW001P01 p01
JOIN PW001P03 p03
ON p03.PIN = p01.PIN
AND p03.DATEFROM > GETDATE()
AND p03.CODE IN (SELECT CODE
FROM PW001C12
WHERE OPTIONS LIKE '%S%')
AND NOT EXISTS (SELECT 1
FROM PW001P03 t
WHERE t.PIN = p01.Pin
AND t.DATEFROM > GETDATE()
AND t.CODE IN (SELECT CODE
FROM PW001C12
WHERE OPTIONS LIKE '%S%')
AND t.DATEFROM < p03.DateFrom
)
left join pw001p08 pass on pass.pin = p01.pin and pass.code = 'PP'
left join pwcountry natcountry on natcountry.countrycode = p01.nationality
LEFT JOIN PWORG orgves ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
LEFT JOIN PWORGVES VES ON orgves.NUMORGID = ves.NUMORGID
LEFT JOIN PWORG orgpos ON orgpos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 5)
LEFT JOIN PWCCMCHANGECREWLIST ccl ON ccl.NEXTACT = p03.SEQUENCENO
LEFT JOIN PWORGVESACT vaOn ON vaOn.SEQNO = ccl.SEQNO
LEFT JOIN PWPORT portOn ON portOn.PORTCODE = vaOn.PLACEFROM
LEFT JOIN PWCOUNTRY countryOn ON countryOn.COUNTRYCODE = portOn.COUNTRYCODE