CREATE VIEW dbo.CrewData_API
as
Select
P01.PIN,
P01.FIRSTNAME,
P01.LASTNAME,
P01.SEX GENDER,
C02.NAME RANK,
P01.NATIONALITY,
P01.BIRTHDATE,
P01.PLACEOFBIRTH,
P1P.PASSPORTNO PASSPORTNO, 
P1P.PASSPORTEXPIRYDATE PASSPORTEXPIRY,
P1P.PASSPORTISSUEDATE,
P1P.PASSPORTISSUECOUNTRY,
P0T.TELENO EMAIL,
MOBILE.TELENO MOBILE

--Status signed on the vessel or signed off - TBA

From PW001P01 P01
LEFT JOIN PW001C02 C02 ON C02.CODE=P01.RANK
LEFT JOIN PW001P1P P1P ON P01.PIN=P1P.PIN and p1p.code = 'PP'
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 MOBILE ON ((P01.PIN = MOBILE.PIN) AND (MOBILE.TELETYPE = 3) AND (NOT MOBILE.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T MOBILE2
WHERE  (MOBILE.PIN = MOBILE2.PIN) AND (MOBILE2.TELETYPE = 3) AND ((MOBILE2.TELEPRIORITY < MOBILE.TELEPRIORITY) OR
((MOBILE2.TELEPRIORITY = MOBILE.TELEPRIORITY) AND (MOBILE2.SEQUENCENO < MOBILE.SEQUENCENO)))))

--WHERE P01.EMPLOYMENTENDDATE IS NULL OR P01.EMPLOYMENTENDDATE > GETDATE() --when enabled - only for active crew

GO

DROP VIEW dbo.CrewData_API
GO