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,
       CASE 
            WHEN p03Cur.SEQUENCENO IS NOT NULL THEN 'Y'
            ELSE 'N'
       END                     AS ONBOARDSTATUS,
	   p03Cur.SEQUENCENO		ONB_ACTIVITY_SEQUENCENO
       --Status signed on the vessel or signed off - TBA
FROM   PW001P01 P01
       LEFT JOIN PW001P03 p03Cur
            ON  P01.PIN = p03Cur.PIN
            AND p03Cur.DATETO IS NULL
            AND ISNULL(p03Cur.PLANNED, 'N') <> 'Y'
            AND EXISTS(
                    SELECT 1
                    FROM   PW001C12 t1
                    WHERE  t1.CODE = p03Cur.CODE
                           AND t1.OPTIONS LIKE '%S%'
                )
       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