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',
       (
           SELECT CONVERT(
                      VARCHAR(999),
                      REPLACE(
                          REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
                          CHAR(10),
                          ''
                      )
                  )
           FROM   PW001P0N Personal
           WHERE  Personal.PIN = p01.PIN
                  AND Personal.FIELDNO = 'p17'
       )                                  AS 'Notes - Personal Details'
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 PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
            AND p03plan.PLANNED = 'Y'
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           AND p03t.PLANNED = 'Y'
                           AND p03t.DATEFROM < p03plan.DateFrom
                )
       LEFT JOIN PW001C12 c12plan
            ON  c12plan.CODE = p03plan.CODE
       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
       LEFT JOIN PW001P0U p0u2
            ON  p0u2.PIN = p01.PIN
            AND NOT EXISTS (
                    SELECT 1
                    FROM   PW001P03 p03t
                    WHERE  p03t.PIN = p01.PIN
                           --AND p03t.PLANNED = 'N'
                           AND p03t.CODE IN (SELECT t.CODE
                                             FROM   PW001C12 t
                                             WHERE  t.OPTIONS LIKE '%S%')
                               --AND p03t.DATEFROM < CAST(GETDATE() AS DATE)
                )