CREATE VIEW dbo.PW001SRV20 AS
SELECT P01.PIN PIN, P01.NAME NAME,
P01.TITLENAME TITLENAME,
P01.FIRSTNAME FIRSTNAME,
P01.MIDDLENAME MIDDLENAME,
P01.LASTNAME LASTNAME,
P01.MAIDENNAME MAIDENNAME,
P01.CALLINGNAME CALLINGNAME,
P01.SUFFIXNAME SUFFIXNAME,
C02.NAME RANK, C02.GROUPNO RANKSORT,
P01.NATIONALITY NATIONALITY,
P01.CLIENT NUMORGID,
ORG.NAME ORGNAME,
PV.NAME PAYROLLVESSEL,
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO,
P01.TELEPHONE PHONE,
P1P.PASSPORTEXPIRYDATE PASSPORTEXPIRY,
P1P.PASSPORTNO PASSPORTNO,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
P0P.STARTDATE P0PSTARTDATE,
P0P.ENDDATE P0PENDDATE,
P01.PERSONGROUPCODE PERSONGROUP,
C38.GROUPNO PAYROLLSTATUSSORTNO,
C32.TEXT CONTRACTTYPE,
P01.CONTRACTSTARTDATE,
P01.CONTRACTEXPIRYDATE,
P01.ADDRESS1,
P01.ADDRESS2,
P01.ADDRESS3,
P01.POSTCODE,
P01.POSTPLACE,
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P01.HOMEAIRPORT,
P01.HOMEAIRPORT2,
P01.HOMEAIRPORT3,
P0T.TELENO EMAIL,
PL.DOCUMENT PPICTURE
FROM
PW001P01 P01
LEFT JOIN PW001P1P P1P ON P01.PIN=P1P.PIN
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
LEFT JOIN PW001C32 C32 ON P01.CONTRACTTYPE=C32.CODE
LEFT JOIN PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL ON P01.PIN = PL.PIN
LEFT JOIN PWORG PV ON P01.PAYROLLVSL=PV.NUMORGID
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))))) |