CREATE VIEW dbo.VIEWIDPW001SRV12
AS

SELECT P01.PIN                               PIN,
       P01.ALTERNATIVEPINCASE 
            WHEN c12.[TEXT] IS ASNOT 'ALTERNATIVE PIN',
       P01.NAMENULL THEN c12.[TEXT]
            WHEN p03plan.PIN IS NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] +  AS 'Full Name', + CONVERT(VARCHAR, p03plan.DATEFROM, 1)
   P01.TITLENAME         WHEN p0u2.AVPLAN IS NOT          ASNULL THEN 'TitleAvailable NamePlanning',
       P01.FIRSTNAME     WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NOT NULL THEN 'Available to  Return'
   AS 'First Name',       WHEN P01p0u2.MIDDLENAMEAVPLAN IS NULL AND p0u2.AVRET IS NULL THEN 'Available'
       END     AS 'Middle Name',        P01.LASTNAME                       AS 'Last Name'AS [STATUS],
       P01.MAIDENNAME    ALTERNATIVEPIN                 AS 'MaidenALTERNATIVE NamePIN',
       CASEpass.nativename              WHEN p01.SEX = 'M' THEN 'Male' AS 'Native Name',
       P01.NAME  WHEN p01.SEX = 'F' THEN 'Female'             ELSE 'Undefined'      AS 'Full ENDName',
         P01.TITLENAME                      AS Gender'Title Name',
       p01Rank.[NAME]P01.FIRSTNAME                      AS 'CurrentFirst RankName',
       --	P01.CALLINGNAME AS 'Calling Name',MIDDLENAME                    --	P01.SUFFIXNAME AS 'SuffixMiddle Name',
       ORG.NAME   P01.LASTNAME                        AS 'OrganizationLast Name',
       --	C02.GROUPNO AS 'Rank Sort',P01.MAIDENNAME           P01.CLIENT          AS 'Maiden Name',
       CASE 
      Numorgid,      WHEN p01.SEX  P01.EmploymentStartDate,
 = 'M' THEN 'Male'
     P01.EmploymentEndDate,       WHEN natp01.NATIONALITYSEX = 'F' THEN 'Female'
            ELSE 'Undefined'
 AS  Nationality,    END    --	PV.NAME PAYROLLVESSEL,         P01.BIRTHDATE 'Birth date',        P01.PERSONALIDNO        AS Gender,
       c04.[TEXT]  AS 'Personal ID',        PASS.TDNUMBER 'Passport No             AS 'Marital Status',
       PASSnat.DATETONATIONALITY                    AS Nationality,
   AS 'Passport Expiry',  ORG.NAME      passc.COUNTRYNAME                  AS 'Passport Country ofAS Issue'Organization',
       --	P0P.STARTDATE P0PSTARTDATE,P01.CLIENT         --	P0P.ENDDATE P0PENDDATE,        --	P01.PERSONGROUPCODE 'Cost Group',        --	c33.Name AS 'Category A',Numorgid,
       P01.EmploymentStartDate,
  P01.ADDRESS1 + ' ' + P01.ADDRESS2EmploymentEndDate,
  AS 'Address',    c55.NAME    P01.ADDRESS3                       AS 'CityTermination Reason',
       P01p01Rank.POSTCODE[NAME] 'Post Code',        P01.POSTPLACE 'Post Place',        PC.COUNTRYNAME AS 'Current Rank',
       p01.COSTPLACE          AS 'Address Country',        P01.HOMEAIRPORT  AS 'Home AirportDepartment/Cost Place Code',
       --	P01.HOMEAIRPORT2,c43.[NAME]            email.TELENO             AS 'Department/Cost Place',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'E-MAILAddress',
       mobP01.TELENOADDRESS3                         AS 'Mobile PhoneCity',
       PLP01.DOCUMENTPOSTCODE 'Post Code',
        P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME      PPICTURE,        CASE       AS 'Address  Country',
   WHEN CONTRACTKIND = 0 THENP01.HOMEAIRPORT 'MainHome ContractAirport',
       email.TELENO     WHEN CONTRACTKIND = 1 THEN 'Sub Contract'             WHEN CONTRACTKIND = 2 THEN AS 'AmmendmentE-MAIL',
       mob.TELENO     ELSE ''        END           AS 'Mobile Phone',
       P01.BIRTHDATE 'Birth date',
        AS 'Contract Type',P01.PERSONALIDNO         C32CONT.Text 'Contract Name',        C02CONT.NAMEAS 'ContractPersonal RankID',
       p20PASS.DATESTARTTDNUMBER 'ContractPassport StartNo',
       p20.DATEEND 'Contract End',PASS.DATETO             p20.DURATION 'Trial period duration',        p20.TRIALPERIODENDAS 'TrialPassport period endExpiry',
       p20passc.PAYSCALETABLECOUNTRYNAME 'Contract Pasycale Table',        p20.PAYSCALECODE 'Contract Pasycale Code',        p0u.EMP_PORTAS 'EmployeePassport PortalCountry Accessof FromIssue',
       CASE 
            WHEN p20.scanneddocnoCONTRACTKIND IS= NULL0 THEN NCHAR(9940) + 'Main MissingContract'
            WHEN p20.scanvalidityCONTRACTKIND IN= (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + 'Sub ConfirmedContract'
            ELSE 'N/A'
   WHEN CONTRACTKIND = 2 THEN 'Ammendment'
   END         ELSE ''
       END              AS 'Contract Scan Status' FROM   PW001P01 P01        LEFT JOIN (AS 'Contract Type',
       C32CONT.Text 'Contract Name',
    SELECT P8.PIN,  C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
       p20.DATEEND  P8.tdnumber'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial    P8.datefromperiod end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract    P8.datetoPasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
  p8.ISSUEDWHERE,          WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
     p8.nativename,       WHEN  p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
  p8.ISSUE_COUNTRY,          WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not p8.PASSPORTPRIORITYConfirmed'
       END         FROM   dbo.PW001P08 P8                   AS 'Contract Scan Status',
 JOIN dbo.PW001C23 C23    (
           SELECT CONVERT(
           ON  P8.CODE = C23.CODE       VARCHAR(999),
                     AND C23.OPTIONSREPLACE(
LIKE '%T%'                 WHERE  NOT EXISTS (    REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
                     SELECT 1    CHAR(10),
                       FROM   PW001P08''
t                      )
            JOIN dbo.PW001C23 C23    )
           FROM   PW001P0N Personal
           WHERE  Personal.PIN = p01.PIN
   ON  t.CODE = C23.CODE           AND Personal.FIELDNO = 'p17'
       )                  AND C23.OPTIONS LIKE '%T%'             AS 'Notes - Personal Details'
FROM   PW001P01 P01
     WHERE  t.PINLEFT =JOIN p8.PIN(
                SELECT P8.PIN,
                AND (      P8.tdnumber,
                       P8.datefrom,
            t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY         P8.dateto,
                       p8.ISSUEDWHERE,
         OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY           p8.nativename,
                        p8.ISSUE_COUNTRY,
      AND t.SEQUENCENO < p8.SEQUENCENO              p8.PASSPORTPRIORITY
                FROM   dbo.PW001P08 P8
   )                    JOIN dbo.PW001C23 C23
 )             ) pass             ON  passP8.PINCODE = P01C23.PINCODE
         LEFT JOIN PWCOUNTRY passc             ON  passc.COUNTRYCODE =AND passC23.ISSUE_COUNTRYOPTIONS LIKE '%T%'
     LEFT JOIN PW001P0P P0P        WHERE  NOT EXISTS (
ON  ((P01.PIN  = P0P.PIN) AND (P0P.PNUMBER = 'A'))        LEFT JOIN PW001C02 p01Rank       SELECT 1
    ON  p01Rank.CODE = p01.[RANK]        LEFT JOIN Pw001C33 c33        FROM   PW001P08 t
ON  c33.code = p01.CATHEGORYA        LEFT JOIN PW001C02 C02             ON  P0P.POSITIONID = C02.CODE  JOIN dbo.PW001C23 C23
   LEFT JOIN PWORG ORG             ON  P01.CLIENT = ORG.NUMORGID        LEFT JOIN PWCOUNTRY PC     ON  t.CODE = C23.CODE
   ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE        LEFT JOIN PW001P01PICT PL             ON  P01.PIN = PL.PIN    AND C23.OPTIONS LIKE '%T%'
 LEFT JOIN PWORG PV             ON  P01.PAYROLLVSL = PV.NUMORGID      WHERE  LEFTt.PIN JOIN= PW001P20p8.PIN
p20             ON  p20.PIN = p01.PIN             AND p20.HISTORICAL = 'F' AND (
              --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)             AND NOT EXISTS (      t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
            SELECT 1                     FROM   PW001P20 t    OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
             WHERE  t.PIN = p20.PIN
                           AND t.HISTORICALSEQUENCENO = 'F'< p8.SEQUENCENO
                           AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001OLEDOCS sdoc             ON )
sdoc.DOCNO = p20.SCANNEDDOCNO        LEFT JOIN PW001C02) C02CONTpass
            ON  p20pass.RANKPIN = C02CONTP01.CODEPIN
       LEFT JOIN PW001C32PWCOUNTRY C32CONTpassc
            ON  p20passc.CONTRACTTYPECOUNTRYCODE = C32CONTpass.codeISSUE_COUNTRY
       LEFT JOIN PW001P0TPW001P0P emailP0P
            ON  email((P01.PIN = p01P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT ANDJOIN email.TELETYPEPW001C02 =p01Rank
6            ON AND emailp01Rank.TELEPRIORITYCODE IS NOT NULL= p01.[RANK]
       LEFT JOIN Pw001C33 c33
  AND NOT EXISTS (       ON  c33.code = p01.CATHEGORYA
       LEFT JOIN SELECTPW001C02 1C02
            ON  P0P.POSITIONID = C02.CODE
   FROM   PW001P0T tLEFT JOIN PWORG ORG
            ON     WHERE  t.PINP01.CLIENT = emailORG.PINNUMORGID
       LEFT JOIN PWCOUNTRY PC
            ON    AND t.TELETYPEP01.ADDRESS_COUNTRY = emailPC.TELETYPECOUNTRYCODE
       LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = AND tPL.TELEPRIORITYPIN
IS NOT NULL     LEFT JOIN PWORG PV
            ON  P01.PAYROLLVSL = PV.NUMORGID
  AND (    LEFT JOIN PW001P20 p20
            ON  p20.PIN = p01.PIN
           t.TELEPRIORITY <AND emailp20.TELEPRIORITYHISTORICAL = 'F'
            AND NOT EXISTS (
                  OR t.TELEPRIORITY =SELECT email.TELEPRIORITY1
                    FROM   PW001P20 t
          AND t.SEQUENCENO < email.SEQUENCENO       WHERE  t.PIN = p20.PIN
                    )       AND t.HISTORICAL = 'F'
      )        LEFT JOIN PW001P0T mob             ON  mob.PIN = p01.PINAND t.DATESTART > p20.DATESTART
            AND mob.TELETYPE = 3 )
       LEFT JOIN PW001OLEDOCS sdoc
AND mob.TELEPRIORITY IS NOT NULL        ON  sdoc.DOCNO = p20.SCANNEDDOCNO
      AND NOTLEFT EXISTSJOIN (PW001C02 C02CONT
            ON  p20.RANK = C02CONT.CODE
  SELECT 1    LEFT JOIN PW001C32 C32CONT
            ON FROM p20.CONTRACTTYPE = PW001P0TC32CONT.code
  t     LEFT JOIN PW001P0T email
            WHEREON  temail.PIN = mobp01.PIN
            AND email.TELETYPE = 6
            AND temail.TELETYPETELEPRIORITY IS =NOT mob.TELETYPENULL
            AND NOT EXISTS (
           AND t.TELEPRIORITY IS NOT NULL     SELECT 1
                    FROM AND ( PW001P0T t
                    WHERE  t.PIN = email.PIN
        t.TELEPRIORITY < mob.TELEPRIORITY                 AND t.TELETYPE = email.TELETYPE
               OR t.TELEPRIORITY = mob.TELEPRIORITY         AND t.TELEPRIORITY IS NOT NULL
                      AND t.SEQUENCENO < mob.SEQUENCENO  AND (
                            )       t.TELEPRIORITY < email.TELEPRIORITY
       )        LEFT JOIN PWCOUNTRY nat             ON  nat.COUNTRYCODE = p01.NATIONALITY OR t.TELEPRIORITY = email.TELEPRIORITY
                  LEFT JOIN PW001P0U p0u             ON AND p0ut.PINSEQUENCENO =< p01.PIN

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)
                )