CREATE VIEW dbo.PW001SRV12
AS

SELECT P01.PIN                               PIN,
       CASE 
            WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
            WHEN p0u.AVPLAN IS NOT NULL THEN 'Available Planning'
            WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NOT NULL THEN 'Available to Return'
            WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NULL THEN 'Available'
       END                                AS [STATUS],
       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,
       c04.[TEXT]                         AS 'Marital Status',
       nat.NATIONALITY                    AS Nationality,
       ORG.NAME                           AS 'Organization',
       P01.CLIENT                            Numorgid,
       P01.EmploymentStartDate,
       P01.EmploymentEndDate,
       c55.NAME                           AS 'Termination Reason',
       p01Rank.[NAME]                     AS 'Current Rank',
       p01.COSTPLACE                      AS 'Department/Cost Place Code',
       c43.[NAME]                         AS 'Department/Cost Place',
       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',
       email.TELENO                       AS 'E-MAIL',
       mob.TELENO                         AS 'Mobile Phone',
       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',
       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) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
       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 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
       LEFT JOIN PW001C55 c55
            ON  p01.STOPREASON = c55.CODE
       LEFT JOIN PW001C43 c43
            ON  c43.CODE = p01.COSTPLACE
       LEFT JOIN PW001C04 c04
            ON  c04.CODE = p01.MARITALSTATUS
       LEFT JOIN dbo.PW001P03 P03cur
            ON  P01.PIN = P03cur.PIN
            AND (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())
            AND P03cur.DATEFROM <= GETDATE()
            AND P03cur.PLANNED <> 'Y'
       LEFT JOIN PW001C12 c12
            ON  c12.CODE = p03cur.CODE