CREATE VIEW dbo.PW001SRV12
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 NULL THEN 'Available  Planning'
    AS 'Title Name',       WHEN P01p0u2.FIRSTNAMEAVPLAN IS NULL AND p0u2.AVRET IS NOT NULL THEN 'Available to Return'
          AS 'First Name',
       P01.MIDDLENAME    WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NULL THEN 'Available'
       END         AS 'Middle Name',        P01.LASTNAME             AS [STATUS],
       P01.ALTERNATIVEPIN AS 'Last Name',        P01.MAIDENNAME      AS 'ALTERNATIVE PIN',
       pass.nativename     AS 'Maiden Name',        CASE     AS 'Native Name',
      WHEN p01P01.SEXNAME = 'M' THEN 'Male'             WHEN p01.SEX = 'F' THEN 'Female'     AS 'Full Name',
     ELSE 'Undefined' P01.TITLENAME       END               AS 'Title Name',
       P01.FIRSTNAME       AS Gender, 	   nat.NATIONALITY          AS 'First Name',
       ASP01.MIDDLENAME Nationality, 	   ORG.NAME                AS 'Middle Name',
       P01.LASTNAME AS 'Organization', 	   P01.CLIENT                 AS 'Last Name',
       P01.MAIDENNAME Numorgid,        P01.EmploymentStartDate,        P01.EmploymentEndDate, 	   c55.NAMEAS as 'TerminationMaiden ReasonName',
       p01Rank.[NAME]CASE 
            WHEN p01.SEX = 'M' THEN 'Male'
      AS 'Current Rank', 	   --WHEN p01.COSTPLACESEX as= 'Department/Cost Place',F' THEN 'Female'
           --	P01.CALLINGNAME AS ELSE 'Calling NameUndefined',
       --	P01.SUFFIXNAME AS 'Suffix Name',END           --	C02.GROUPNO AS 'Rank Sort', 	   P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',      AS Gender,
P01.ADDRESS3       c04.[TEXT]                AS 'City',        P01.POSTCODEAS 'PostMarital CodeStatus',
       P01nat.POSTPLACENATIONALITY 'Post Place',        PC.COUNTRYNAME          AS Nationality,
       ORG.NAME   AS 'Address Country',        P01.HOMEAIRPORT 'Home Airport',        --	P01.HOMEAIRPORT2,    AS 'Organization',
       P01.CLIENT    email.TELENO                       AS 'E-MAIL'Numorgid,
       mobP01.TELENOEmploymentStartDate,
       P01.EmploymentEndDate,
       c55.NAME         AS 'Mobile Phone',        --	PV.NAME PAYROLLVESSEL,        AS P01.BIRTHDATE 'BirthTermination dateReason',
       P01.PERSONALIDNOp01Rank.[NAME]                     AS 'PersonalCurrent IDRank',
       PASSp01.TDNUMBERCOSTPLACE 'Passport No',        PASS.DATETO            AS 'Department/Cost Place Code',
        AS 'Passport Expiry',
c43.[NAME]       passc.COUNTRYNAME                  AS 'Passport Country of IssueDepartment/Cost Place',
       --	P0P.STARTDATE P0PSTARTDATE,
       --	P0P.ENDDATE P0PENDDATEP01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       --	P01.PERSONGROUPCODE 'Cost Group',P01.ADDRESS3          --	c33.Name AS 'Category A',        CASE  AS 'City',
       P01.POSTCODE 'Post Code',
WHEN CONTRACTKIND = 0 THEN 'Main Contract' P01.POSTPLACE 'Post Place',
       PC.COUNTRYNAME  WHEN CONTRACTKIND = 1 THEN 'Sub Contract'             WHENAS CONTRACTKIND = 2 THEN 'Ammendment''Address Country',
       P01.HOMEAIRPORT 'Home Airport',
     ELSE '' email.TELENO       END                AS 'E-MAIL',
       mob.TELENO       AS 'Contract Type',        C32CONT.Text 'Contract Name',      AS  C02CONT.NAME 'ContractMobile RankPhone',
       p20P01.DATESTARTBIRTHDATE 'ContractBirth Startdate',
       p20P01.DATEENDPERSONALIDNO 'Contract End',        p20.DURATION 'Trial period duration',      AS  p20.TRIALPERIODEND 'Trial period end'Personal ID',
       PASS.TDNUMBER 'Passport No',
       p20PASS.PAYSCALETABLEDATETO 'Contract Pasycale Table',        p20.PAYSCALECODE 'Contract Pasycale Code',        p0u.EMP_PORT 'Employee PortalAS Access'Passport FromExpiry',
       CASEpassc.COUNTRYNAME              WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'AS 'Passport Country of Issue',
       CASE 
   WHEN  p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOTWHEN NULLCONTRACTKIND THEN NCHAR(10004) += 0 THEN 'Main ConfirmedContract'
            ELSE 'N/A'
    WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
  END          WHEN CONTRACTKIND = 2 THEN 'Ammendment'
            ELSE    AS 'Contract Scan Status'
FROM   PW001P01 P01   END     LEFT JOIN (                 SELECT P8.PIN,       AS 'Contract Type',
       C32CONT.Text 'Contract Name',
    P8.tdnumber,   C02CONT.NAME 'Contract Rank',
       p20.DATESTART 'Contract Start',
        P8p20.datefrom,DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period P8.datetoend',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract    p8.ISSUEDWHEREPasycale Code',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
  p8.nativename,          WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
     p8.ISSUE_COUNTRY,       WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
   p8.PASSPORTPRIORITY         WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT FROMNULL THEN  dbo.PW001P08 P8
 NCHAR(10006) + ' Not Confirmed'
       END              JOIN dbo.PW001C23 C23                AS 'Contract Scan Status',
       (
 ON  P8.CODE = C23.CODE      SELECT CONVERT(
                     AND C23.OPTIONS LIKE '%T%' VARCHAR(999),
                  WHERE  NOT EXISTS REPLACE(
                           SELECT 1REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
                          CHAR(10),
FROM   PW001P08 t                      ''
            JOIN dbo.PW001C23 C23        )
                  )
           FROM ON  t.CODEPW001P0N =Personal
C23.CODE           WHERE  Personal.PIN      = p01.PIN
                    AND C23Personal.OPTIONSFIELDNO LIKE= '%T%p17'
       )                    WHERE  t.PIN = p8.PIN          AS 'Notes - Personal Details'
FROM   PW001P01 P01
       LEFT JOIN (
     AND (          SELECT P8.PIN,
                       P8.tdnumber,
       t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY              P8.datefrom,
                       P8.dateto,
    OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY                p8.ISSUEDWHERE,
                       p8.nativename,
  AND t.SEQUENCENO < p8.SEQUENCENO                  p8.ISSUE_COUNTRY,
                    )   p8.PASSPORTPRIORITY
                FROM   dbo.PW001P08 P8
)             ) pass         JOIN dbo.PW001C23 C23
 ON  pass.PIN = P01.PIN        LEFT JOIN PWCOUNTRY passc             ON  passcP8.COUNTRYCODECODE = passC23.ISSUE_COUNTRYCODE
       LEFT JOIN PW001P0P P0P             ON  ((P01.PIN = P0P.PIN) AND (P0PC23.PNUMBEROPTIONS =LIKE 'A%T%'))
        LEFT JOIN PW001C02 p01Rank     WHERE  NOT EXISTS (
   ON  p01Rank.CODE = p01.[RANK]        LEFT JOIN Pw001C33 c33         SELECT 1
  ON  c33.code = p01.CATHEGORYA        LEFT JOIN PW001C02 C02          FROM   ON  P0P.POSITIONID = C02.CODE
PW001P08 t
      LEFT JOIN PWORG ORG             ON  P01.CLIENT = ORG.NUMORGID        LEFT JOIN PWCOUNTRYdbo.PW001C23 PCC23
            ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE        LEFT JOIN PW001P01PICT PL             ON  P01t.PINCODE = PLC23.PINCODE
       LEFT JOIN PWORG PV             ON  P01.PAYROLLVSL = PV.NUMORGID        LEFT JOIN PW001P20 p20
  AND C23.OPTIONS LIKE '%T%'
           ON  p20.PIN = p01.PIN            WHERE AND p20t.HISTORICALPIN = 'F'p8.PIN
                --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)             AND NOT EXISTS (
                    SELECT 1                     FROMt.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
PW001P20 t                     WHERE  t.PIN = p20.PIN                OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
           AND t.HISTORICAL = 'F'                            AND t.DATESTARTSEQUENCENO >< p20p8.DATESTARTSEQUENCENO
                )        LEFT JOIN PW001OLEDOCS sdoc           )
 ON  sdoc.DOCNO = p20.SCANNEDDOCNO        LEFT JOIN PW001C02 C02CONT        )
    ON  p20.RANK = C02CONT.CODE    ) pass
  LEFT JOIN PW001C32 C32CONT             ON  p20pass.CONTRACTTYPEPIN = C32CONTP01.codePIN
       LEFT JOIN PW001P0TPWCOUNTRY emailpassc
            ON  emailpassc.PINCOUNTRYCODE = p01pass.PINISSUE_COUNTRY
       LEFT JOIN PW001P0P P0P
 AND email.TELETYPE = 6        ON  ((P01.PIN = P0P.PIN) AND email(P0P.TELEPRIORITYPNUMBER IS NOT NULL= 'A'))
       LEFT JOIN PW001C02 p01Rank
  AND NOT EXISTS (       ON  p01Rank.CODE = p01.[RANK]
       LEFT JOIN SELECTPw001C33 1c33
            ON  c33.code = p01.CATHEGORYA
   FROM   PW001P0T tLEFT JOIN PW001C02 C02
            ON     WHERE  t.PINP0P.POSITIONID = emailC02.PINCODE
       LEFT JOIN PWORG ORG
            ON    AND t.TELETYPEP01.CLIENT = emailORG.TELETYPENUMORGID
       LEFT JOIN PWCOUNTRY PC
            ON  P01.ADDRESS_COUNTRY = AND tPC.TELEPRIORITYCOUNTRYCODE
IS NOT NULL     LEFT JOIN PW001P01PICT PL
            ON  P01.PIN = PL.PIN
  AND (    LEFT JOIN PWORG PV
            ON  P01.PAYROLLVSL = PV.NUMORGID
       LEFT JOIN PW001P20 p20
t.TELEPRIORITY < email.TELEPRIORITY          ON  p20.PIN = p01.PIN
            AND p20.HISTORICAL = 'F'
     OR t.TELEPRIORITY = email.TELEPRIORITY    AND NOT EXISTS (
                    SELECT 1
      AND t.SEQUENCENO < email.SEQUENCENO           FROM   PW001P20 t
                )    WHERE  t.PIN = p20.PIN
        )        LEFT JOIN PW001P0T mob        AND t.HISTORICAL = 'F'
  ON  mob.PIN = p01.PIN             AND mob.TELETYPE = 3     AND t.DATESTART > p20.DATESTART
    AND mob.TELEPRIORITY IS NOT NULL        )
    AND NOT EXISTS (LEFT JOIN PW001OLEDOCS sdoc
            ON  sdoc.DOCNO = p20.SCANNEDDOCNO
SELECT 1      LEFT JOIN PW001C02 C02CONT
           FROM ON  PW001P0Tp20.RANK t= C02CONT.CODE
       LEFT JOIN PW001C32 C32CONT
        WHERE  t.PIN = mob.PINON  p20.CONTRACTTYPE = C32CONT.code
       LEFT JOIN PW001P0T email
            ON AND temail.TELETYPEPIN = mobp01.TELETYPEPIN
            AND email.TELETYPE = 6
            AND temail.TELEPRIORITY IS NOT NULL
            AND NOT EXISTS (
           AND (        SELECT 1
                    FROM   PW001P0T   t.TELEPRIORITY
< mob.TELEPRIORITY                   WHERE  t.PIN = email.PIN
            OR t.TELEPRIORITY = mob.TELEPRIORITY            AND t.TELETYPE = email.TELETYPE
                    AND t.SEQUENCENO < mob.SEQUENCENO    AND t.TELEPRIORITY IS NOT NULL
                       )    AND (
           )        LEFT JOIN PWCOUNTRY nat             ONt.TELEPRIORITY < nat.COUNTRYCODE = p01.NATIONALITYemail.TELEPRIORITY
                 LEFT JOIN PW001P0U p0u             ON  p0uOR t.PINTELEPRIORITY = p01.PIN
	email.TELEPRIORITY
         LEFT JOIN PW001C55 c55             ON  p01.STOPREASON = c55.CODE      

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