CREATE VIEW dbo.VIEWIDPW001SRV12
AS

SELECT P01.PIN                               PIN,
       P01.ALTERNATIVEPINCASE 
            WHEN c12.[TEXT] IS ASNOT 'ALTERNATIVE PIN',
  NULL THEN c12.[TEXT]
    P01.NAME        WHEN p03plan.PIN IS NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] + ' ' + CONVERT(VARCHAR, p03plan.DATEFROM, 1)
AS 'Full Name',        P01.TITLENAME  WHEN p0u2.AVPLAN IS NOT NULL THEN 'Available Planning'
            AS 'Title Name',
       P01.FIRSTNAME  WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NOT NULL THEN 'Available to Return'
            WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS ASNULL THEN 'Available'First Name',
       END    P01.MIDDLENAME                     AS 'Middle Name',     AS [STATUS],
 P01.LASTNAME      P01.ALTERNATIVEPIN                 AS 'LastALTERNATIVE NamePIN',
       P01pass.MAIDENNAME nativename                    AS 'MaidenNative Name',
       CASEP01.NAME              WHEN p01.SEX = 'M' THEN 'Male'        AS 'Full Name',
  WHEN p01.SEX = 'F' THEN 'Female' P01.TITLENAME             ELSE 'Undefined'        ENDAS 'Title Name',
       P01.FIRSTNAME                      AS Gender'First Name',
       p01RankP01.[NAME]MIDDLENAME                     AS 'CurrentMiddle RankName',
       --	P01.CALLINGNAMELASTNAME AS 'Calling Name',        --	P01.SUFFIXNAME AS 'Suffix Name',        ORG.NAME AS 'Last Name',
          P01.MAIDENNAME             AS 'Organization',        --	C02.GROUPNO AS 'RankMaiden SortName',
       P01.CLIENTCASE 
            WHEN p01.SEX = 'M' THEN 'Male'
        Numorgid,    WHEN p01.SEX =  P01.EmploymentStartDate,
'F' THEN 'Female'
      P01.EmploymentEndDate,      ELSE 'Undefined'
nat.NATIONALITY       END             AS Nationality,        --	PV.NAME PAYROLLVESSEL,         P01.BIRTHDATE 'BirthAS date'Gender,
       P01c04.PERSONALIDNO[TEXT]                   AS 'Personal ID',        PASS.TDNUMBERAS 'PassportMarital NoStatus',
       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.HOMEAIRPORTAS 'Home AirportDepartment/Cost Place Code',
       --	P01.HOMEAIRPORT2, c43.[NAME]         email.TELENO                       AS 'E-MAILDepartment/Cost Place',
       mobP01.TELENOADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.ADDRESS3          AS 'Mobile Phone',        PL.DOCUMENT   AS 'City',
       P01.POSTCODE 'Post Code',
       P01.POSTPLACE 'Post Place',
  PPICTURE,     PC.COUNTRYNAME   CASE              WHEN CONTRACTKIND = 0 THENAS 'MainAddress ContractCountry',
       P01.HOMEAIRPORT 'Home Airport',
  WHEN CONTRACTKIND = 1 THEN 'Sub Contract' email.TELENO                   WHEN CONTRACTKIND = 2 THENAS 'AmmendmentE-MAIL',
       mob.TELENO     ELSE ''        END           AS 'Mobile Phone',
       P01.BIRTHDATE 'Birth date',
        AS 'Contract Type',P01.PERSONALIDNO          C32CONT.Text 'Contract Name',       AS C02CONT.NAME 'ContractPersonal RankID',
       p20PASS.DATESTARTTDNUMBER 'ContractPassport StartNo',
       p20PASS.DATEEND 'Contract End',DATETO             p20.DURATION 'Trial period duration',        p20.TRIALPERIODENDAS 'TrialPassport period endExpiry',
       p20.PAYSCALETABLE 'Contract Pasycale Table',passc.COUNTRYNAME        p20.PAYSCALECODE 'Contract Pasycale Code',        p0u.EMP_PORT 'Employee Access FromAS 'Passport Country of Issue',
       CASE 
            WHEN p20.scanneddocnoCONTRACTKIND IS= NULL0 THEN NCHAR(9940) + ' Missing''Main Contract'
            WHEN CONTRACTKIND = 1 THEN  WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'Sub Contract'
            WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE 'N/A'
       END                                AS 'Contract Scan StatusType',
FROM   PW001P01 P01   C32CONT.Text 'Contract Name',
  LEFT JOIN (   C02CONT.NAME 'Contract Rank',
       p20.DATESTART    SELECT P8.PIN'Contract Start',
       p20.DATEEND 'Contract End',
       p20.DURATION 'Trial     P8.tdnumberperiod duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract    P8.datefromPasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
            P8.datetop0u.EMP_PORT 'Employee Portal Access From',
       CASE 
            WHEN  p8.ISSUEDWHERE,
     p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN p20.scanvalidity IN (1) AND p8p20.nativename,scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
            WHEN   p8.ISSUE_COUNTRY,
            p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
       END   p8.PASSPORTPRIORITY                 FROM   dbo.PW001P08 P8        AS 'Contract Scan Status',
       (
    JOIN dbo.PW001C23 C23     SELECT CONVERT(
                      ONVARCHAR(999),
 P8.CODE = C23.CODE                   REPLACE(
         AND C23.OPTIONS LIKE '%T%'              REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
   WHERE  NOT EXISTS (                   CHAR(10),
        SELECT 1                 ''
          FROM   PW001P08 t        )
                  )
       JOIN dbo.PW001C23 C23  FROM   PW001P0N Personal
           WHERE  Personal.PIN = p01.PIN
                 ON AND tPersonal.CODEFIELDNO = C23.CODE'p17'
       )                                AND C23.OPTIONS LIKEAS '%T%Notes - Personal Details'
FROM   PW001P01 P01
       LEFT JOIN (
            WHERE  t.PIN = p8SELECT P8.PIN,
                       P8.tdnumber,
          AND (            P8.datefrom,
                       P8.dateto,
      t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY               p8.ISSUEDWHERE,
                           OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITYp8.nativename,
                       p8.ISSUE_COUNTRY,
                    AND t.SEQUENCENO < p8.SEQUENCENOPASSPORTPRIORITY
                FROM   dbo.PW001P08 P8
                 )      JOIN dbo.PW001C23 C23
               )             )ON pass P8.CODE = C23.CODE
         ON  pass.PIN = P01.PIN        LEFT JOIN PWCOUNTRY passc    AND C23.OPTIONS LIKE '%T%'
     ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY       WHERE LEFT JOINNOT PW001P0PEXISTS P0P(
            ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))       SELECT LEFT1
JOIN PW001C02 p01Rank             ON  p01Rank.CODE = p01.[RANK]        LEFTFROM JOIN Pw001C33 c33PW001P08 t
           ON  c33.code = p01.CATHEGORYA        LEFT JOIN PW001C02 C02        JOIN dbo.PW001C23 C23
  ON  P0P.POSITIONID = C02.CODE        LEFT JOIN PWORG ORG             ON  P01.CLIENT = ORG.NUMORGID     ON  t.CODE LEFT= JOINC23.CODE
PWCOUNTRY PC             ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE        LEFT JOIN PW001P01PICT PL          AND C23.OPTIONS LIKE ON'%T%'
 P01.PIN = PL.PIN        LEFT JOIN PWORG PV             ONWHERE  P01t.PAYROLLVSLPIN = PVp8.NUMORGIDPIN
       LEFT JOIN PW001P20 p20             ON  p20.PIN = p01.PIN       AND (
    AND p20.HISTORICAL = 'F'                 --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)            t.PASSPORTPRIORITY AND< NOTp8.PASSPORTPRIORITY
EXISTS (                     SELECT 1                   OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
FROM   PW001P20 t                     WHERE  t.PIN = p20.PIN             AND t.SEQUENCENO < p8.SEQUENCENO
           AND t.HISTORICAL = 'F'                        )
   AND t.DATESTART > p20.DATESTART                 )
          LEFT JOIN PW001OLEDOCS) sdocpass
            ON  sdocpass.DOCNOPIN = p20P01.SCANNEDDOCNOPIN
       LEFT JOIN PW001C02PWCOUNTRY C02CONTpassc
            ON  p20passc.RANKCOUNTRYCODE = C02CONTpass.CODEISSUE_COUNTRY
       LEFT JOIN PW001C32PW001P0P C32CONTP0P
            ON  p20((P01.CONTRACTTYPEPIN = C32CONT.code P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001P0TPW001C02 emailp01Rank
            ON  emailp01Rank.PINCODE = p01.PIN[RANK]
       LEFT JOIN Pw001C33 c33
 AND email.TELETYPE = 6         ON  c33.code = AND emailp01.TELEPRIORITYCATHEGORYA
IS NOT NULL     LEFT JOIN PW001C02 C02
    AND NOT EXISTS (     ON  P0P.POSITIONID = C02.CODE
       LEFT JOIN PWORG ORG
SELECT 1           ON  P01.CLIENT = ORG.NUMORGID
     FROM  LEFT PW001P0TJOIN tPWCOUNTRY PC
            ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
  WHERE  t.PIN = email.PIN LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
      AND t.TELETYPELEFT =JOIN email.TELETYPEPWORG PV
            ON  P01.PAYROLLVSL = PV.NUMORGID
       LEFT JOIN ANDPW001P20 t.TELEPRIORITYp20
IS NOT NULL          ON  p20.PIN = p01.PIN
             AND (p20.HISTORICAL = 'F'
            AND NOT EXISTS (
                 t.TELEPRIORITY < email.TELEPRIORITY SELECT 1
                    FROM   PW001P20 t
        OR t.TELEPRIORITY = email.TELEPRIORITY         WHERE  t.PIN = p20.PIN
                      AND t.SEQUENCENO < email.SEQUENCENO  AND t.HISTORICAL = 'F'
                          ) AND t.DATESTART > p20.DATESTART
                )
       LEFT JOIN PW001P0TPW001OLEDOCS mobsdoc
            ON  mobsdoc.PINDOCNO = p01p20.PINSCANNEDDOCNO
       LEFT JOIN PW001C02 C02CONT
 AND mob.TELETYPE = 3        ON  p20.RANK =  AND mob.TELEPRIORITY IS NOT NULLC02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
    AND NOT EXISTS (     ON  p20.CONTRACTTYPE = C32CONT.code
       LEFT JOIN PW001P0T email
SELECT 1           ON  email.PIN = p01.PIN
     FROM   PW001P0T t   AND email.TELETYPE = 6
            AND email.TELEPRIORITY IS WHERENOT NULL
t.PIN = mob.PIN          AND NOT EXISTS (
              AND t.TELETYPE = mob.TELETYPE   SELECT 1
                    FROM   ANDPW001P0T t.TELEPRIORITY
IS NOT NULL                  WHERE  t.PIN = email.PIN
     AND (                     AND t.TELETYPE = email.TELETYPE
           t.TELEPRIORITY  < mob.TELEPRIORITY             AND t.TELEPRIORITY IS NOT NULL
                  OR t.TELEPRIORITY = mob.TELEPRIORITY
         AND (
                                  AND t.SEQUENCENOTELEPRIORITY < mobemail.SEQUENCENOTELEPRIORITY
                               )    OR t.TELEPRIORITY = email.TELEPRIORITY
         )         LEFT JOIN PWCOUNTRY nat             ON AND natt.COUNTRYCODESEQUENCENO =< p01.NATIONALITYemail.SEQUENCENO
               LEFT JOIN PW001P0U p0u             ON)
 p0u.PIN = p01.PIN

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