CREATE VIEW dbo.VIEWID
AS

SELECT P01.PIN                               PIN,
       P01.ALTERNATIVEPIN                 AS 'ALTERNATIVE PIN',
       P01.NAME                           AS 'Full Name',
       P01.TITLENAME                      AS 'Title Name',
       P01.FIRSTNAME                      AS 'First Name',
       P01.MIDDLENAME                     AS 'Middle Name',
       P01.LASTNAME                       AS 'Last Name',
       P01.MAIDENNAME                     AS 'Maiden Name',
       CASE 
            WHEN p01.SEX = 'M' THEN 'Male'
            WHEN p01.SEX = 'F' THEN 'Female'
            ELSE 'Undefined'
       END                                AS Gender,
       p01Rank.[NAME]                     AS 'Current Rank',
       --	P01.CALLINGNAME AS 'Calling Name',
       --	P01.SUFFIXNAME AS 'Suffix Name',
       ORG.NAME                           AS 'Organization',
       --	C02.GROUPNO AS 'Rank Sort',
       P01.CLIENT                            Numorgid,
       P01.EmploymentStartDate,
       P01.EmploymentEndDate,
       nat.NATIONALITY                    AS Nationality,
       --	PV.NAME PAYROLLVESSEL, 
       P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO                   AS 'Personal ID',
       PASS.TDNUMBER 'Passport No',
       PASS.DATETO                        AS 'Passport Expiry',
       passc.COUNTRYNAME                  AS 'Passport Country of Issue',
       --	P0P.STARTDATE P0PSTARTDATE,
       --	P0P.ENDDATE P0PENDDATE,
       --	P01.PERSONGROUPCODE 'Cost Group',
       --	c33.Name AS 'Category A',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.ADDRESS3                       AS 'City',
       P01.POSTCODE 'Post Code',
       P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME                     AS 'Address Country',
       P01.HOMEAIRPORT 'Home Airport',
       --	P01.HOMEAIRPORT2,  
       email.TELENO                       AS 'E-MAIL',
       mob.TELENO                         AS 'Mobile Phone',
       PL.DOCUMENT                           PPICTURE,
       CASE 
            WHEN CONTRACTKIND = 0 THEN 'Main Contract'
            WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
            WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE ''
       END                                AS 'Contract Type',
       C32CONT.Text 'Contract Name',
       C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
       p20.DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
            WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            ELSE 'N/A'
       END                                AS 'Contract Scan Status'
FROM   PW001P01 P01
       LEFT JOIN (
                SELECT P8.PIN,
                       P8.tdnumber,
                       P8.datefrom,
                       P8.dateto,
                       p8.ISSUEDWHERE,
                       p8.nativename,
                       p8.ISSUE_COUNTRY,
                       p8.PASSPORTPRIORITY
                FROM   dbo.PW001P08 P8
                       JOIN dbo.PW001C23 C23
                            ON  P8.CODE = C23.CODE
                            AND C23.OPTIONS LIKE '%T%'
                WHERE  NOT EXISTS (
                           SELECT 1
                           FROM   PW001P08 t
                                  JOIN dbo.PW001C23 C23
                                       ON  t.CODE = C23.CODE
                                       AND C23.OPTIONS LIKE '%T%'
                           WHERE  t.PIN = p8.PIN
                                  AND (
                                          t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
                                          OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
                                          AND t.SEQUENCENO < p8.SEQUENCENO
                                      )
                       )
            ) pass
            ON  pass.PIN = P01.PIN
       LEFT JOIN PWCOUNTRY passc
            ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY
       LEFT JOIN PW001P0P P0P
            ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001C02 p01Rank
            ON  p01Rank.CODE = p01.[RANK]
       LEFT JOIN Pw001C33 c33
            ON  c33.code = p01.CATHEGORYA
       LEFT JOIN PW001C02 C02
            ON  P0P.POSITIONID = C02.CODE
       LEFT JOIN PWORG ORG
            ON  P01.CLIENT = ORG.NUMORGID
       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 PW001P20 p20
            ON  p20.PIN = p01.PIN
            AND p20.HISTORICAL = 'F'
                --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P20 t
                    WHERE  t.PIN = p20.PIN
                           AND t.HISTORICAL = 'F'
                           AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001OLEDOCS sdoc
            ON  sdoc.DOCNO = p20.SCANNEDDOCNO
       LEFT JOIN PW001C02 C02CONT
            ON  p20.RANK = C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
            ON  p20.CONTRACTTYPE = C32CONT.code
       LEFT JOIN PW001P0T email
            ON  email.PIN = p01.PIN
            AND email.TELETYPE = 6
            AND email.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0T t
                    WHERE  t.PIN = email.PIN
                           AND t.TELETYPE = email.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
                           AND (
                                   t.TELEPRIORITY < email.TELEPRIORITY
                                   OR t.TELEPRIORITY = email.TELEPRIORITY
                                   AND t.SEQUENCENO < email.SEQUENCENO
                               )
                )
       LEFT JOIN PW001P0T mob
            ON  mob.PIN = p01.PIN
            AND mob.TELETYPE = 3
            AND mob.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P0T t
                    WHERE  t.PIN = mob.PIN
                           AND t.TELETYPE = mob.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
                           AND (
                                   t.TELEPRIORITY < mob.TELEPRIORITY
                                   OR t.TELEPRIORITY = mob.TELEPRIORITY
                                   AND t.SEQUENCENO < mob.SEQUENCENO
                               )
                )
       LEFT JOIN PWCOUNTRY nat
            ON  nat.COUNTRYCODE = p01.NATIONALITY
       LEFT JOIN PW001P0U p0u
            ON  p0u.PIN = p01.PIN