CREATE VIEW dbo.PW001SRV12
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',       WHEN P01p0u2.TITLENAMEAVPLAN IS NOT NULL THEN 'Available Planning'
            WHEN p0u2.AVPLAN IS ASNULL 'Title Name',
       P01.FIRSTNAMEAND p0u2.AVRET IS NOT NULL THEN 'Available to Return'
            WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NULL THEN 'Available'
 AS 'First Name',    END    P01.MIDDLENAME                     AS 'Middle Name',     AS [STATUS],
 P01.LASTNAME      P01.ALTERNATIVEPIN                 AS 'LastALTERNATIVE NamePIN',
       P01pass.MAIDENNAMEnativename                     AS 'MaidenNative Name',
       c04.[TEXT]P01.NAME                           AS 'MaritalFull StatusName',
       P01.TITLENAME       CASE              WHEN p01.SEXAS = 'M'Title THEN Name'Male',
       P01.FIRSTNAME     WHEN p01.SEX = 'F' THEN 'Female'            AS ELSE'First Name'Undefined',
       P01.MIDDLENAME END                    AS 'Middle Name',
       P01.LASTNAME  AS Gender, 	   nat.NATIONALITY                AS 'Last Name',
  AS Nationality, 	   ORGP01.NAMEMAIDENNAME                     AS 'Maiden Name',
   AS 'Organization', 	  CASE P01.CLIENT
            WHEN p01.SEX = 'M' THEN 'Male'
         Numorgid,   WHEN p01.SEX = 'F'  P01.EmploymentStartDate,THEN 'Female'
       P01.EmploymentEndDate, 	   c55.NAME asELSE 'Termination ReasonUndefined',
       p01Rank.[NAME]END                       AS 'Current Rank', 	   -- p01.COSTPLACE as 'Department/Cost Place'AS Gender,
       --	P01.CALLINGNAME AS 'Calling Name',c04.[TEXT]             --	P01.SUFFIXNAME AS 'Suffix Name',        --	C02.GROUPNO AS 'RankMarital SortStatus',
	   P01.ADDRESS1 + ' ' + P01.ADDRESS2nat.NATIONALITY     AS 'Address',        P01.ADDRESS3      AS Nationality,
       ORG.NAME        AS 'City',        P01.POSTCODE 'Post Code',        P01.POSTPLACEAS 'Post PlaceOrganization',
       PC.COUNTRYNAMEP01.CLIENT                          AS 'Address Country'Numorgid,
       P01.HOMEAIRPORT 'Home Airport'EmploymentStartDate,
       --	P01.HOMEAIRPORT2EmploymentEndDate,
       c55.NAME  email.TELENO                         AS 'E-MAILTermination Reason',
       mob.TELENO  p01Rank.[NAME]                       AS 'MobileCurrent PhoneRank',
       --	PV.NAME PAYROLLVESSEL,p01.COSTPLACE          P01.BIRTHDATE 'Birth date',        P01.PERSONALIDNO  AS 'Department/Cost Place Code',
       c43.[NAME]      AS 'Personal ID',        PASS.TDNUMBER 'Passport No',        PASS.DATETO AS 'Department/Cost Place',
       P01.ADDRESS1 + ' ' + P01.ADDRESS2  AS 'Address',
       P01.ADDRESS3  AS 'Passport Expiry',        passc.COUNTRYNAME           AS 'City',
     AS 'Passport Country of IssueP01.POSTCODE 'Post Code',
       --	P0P.STARTDATE P0PSTARTDATEP01.POSTPLACE 'Post Place',
       --	P0P.ENDDATE P0PENDDATE,PC.COUNTRYNAME          --	P01.PERSONGROUPCODE 'Cost Group',        --	c33.Name AS 'CategoryAddress ACountry',
       CASEP01.HOMEAIRPORT 'Home Airport',
           WHEN CONTRACTKIND = 0 THEN 'Main Contract'email.TELENO                   WHEN CONTRACTKIND = 1 THENAS 'Sub Contract'E-MAIL',
       mob.TELENO     WHEN CONTRACTKIND = 2 THEN 'Ammendment'             ELSE '' AS 'Mobile Phone',
    END   P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO                   AS 'ContractPersonal TypeID',
       C32CONTPASS.TextTDNUMBER 'ContractPassport NameNo',
       C02CONT.NAME 'Contract Rank',PASS.DATETO              p20.DATESTART 'Contract Start',        p20.DATEENDAS 'ContractPassport EndExpiry',
       p20passc.DURATIONCOUNTRYNAME 'Trial period duration',        p20.TRIALPERIODEND 'Trial period end',    AS 'Passport Country  p20.PAYSCALETABLE 'Contract Pasycale Table',of Issue',
       CASE 
 p20.PAYSCALECODE  'Contract Pasycale Code',       WHEN p0u.EMP_PORT 'Employee Portal Access From',
CONTRACTKIND = 0 THEN 'Main Contract'
      CASE      WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
 WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'   WHEN CONTRACTKIND = 2 THEN 'Ammendment'
    WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOTELSE NULL''
 THEN NCHAR(10004) + ' Confirmed'  END           ELSE 'N/A'        END            AS 'Contract Type',
       C32CONT.Text 'Contract Name',
       ASC02CONT.NAME 'Contract Scan StatusRank',
FROM   PW001P01 P01   p20.DATESTART 'Contract Start',
  LEFT JOIN (   p20.DATEEND 'Contract End',
       p20.DURATION 'Trial   SELECT P8.PINperiod duration',
       p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract    P8.tdnumberPasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale Code',
       p0u.EMP_PORT 'Employee Portal   P8.datefromAccess From',
       CASE 
            WHEN  P8p20.dateto,scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
            WHEN    p8.ISSUEDWHERE,
           p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
           p8.nativename, WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
       END p8.ISSUE_COUNTRY,                        p8.PASSPORTPRIORITY       AS 'Contract Scan Status',
      FROM (
 dbo.PW001P08 P8         SELECT CONVERT(
             JOIN dbo.PW001C23 C23         VARCHAR(999),
                   ON  P8.CODE =REPLACE(
C23.CODE                             AND C23.OPTIONS LIKE '%T%'REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
                WHERE  NOT EXISTS (      CHAR(10),
                     SELECT 1    ''
                      )
FROM   PW001P08 t              )
           FROM   PW001P0N Personal
    JOIN dbo.PW001C23 C23     WHERE  Personal.PIN = p01.PIN
                  AND Personal.FIELDNO = 'p17'
       )  ON  t.CODE = C23.CODE                            AS 'Notes - Personal Details'
FROM   PW001P01 P01
  AND C23.OPTIONS LIKE '%T%'  LEFT JOIN (
                SELECT P8.PIN,
     WHERE      t.PIN = p8.PIN          P8.tdnumber,
                       P8.datefrom,
AND  (                     P8.dateto,
                     t.PASSPORTPRIORITY < p8.PASSPORTPRIORITYISSUEDWHERE,
                       p8.nativename,
                  OR t.PASSPORTPRIORITY   = p8.PASSPORTPRIORITYISSUE_COUNTRY,
                       p8.PASSPORTPRIORITY
                FROM  AND tdbo.SEQUENCENOPW001P08 <P8
p8.SEQUENCENO                       JOIN dbo.PW001C23 C23
             )               ON  P8.CODE = C23.CODE
    )             ) pass          AND C23.OPTIONS LIKE ON'%T%'
 pass.PIN = P01.PIN        LEFT JOIN PWCOUNTRY passc  WHERE  NOT EXISTS (
      ON  passc.COUNTRYCODE = pass.ISSUE_COUNTRY        LEFT JOIN PW001P0P P0P      SELECT 1
     ON  ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))        LEFT JOIN PW001C02 p01Rank   FROM   PW001P08 t
     ON  p01Rank.CODE = p01.[RANK]        LEFT JOIN Pw001C33 c33             ON JOIN c33dbo.codePW001C23 =C23
p01.CATHEGORYA        LEFT JOIN PW001C02 C02             ON  P0P.POSITIONID = C02.CODE        LEFT JOIN PWORG ORGON  t.CODE = C23.CODE
        ON    P01.CLIENT = ORG.NUMORGID        LEFT JOIN PWCOUNTRY PC             ON AND P01.ADDRESS_COUNTRY = PC.COUNTRYCODEC23.OPTIONS LIKE '%T%'
           LEFT JOIN PW001P01PICT PL             ONWHERE  P01t.PIN = PLp8.PIN
       LEFT JOIN PWORG PV             ON  P01.PAYROLLVSL = PV.NUMORGID       AND LEFT(
 JOIN PW001P20 p20             ON  p20.PIN = p01.PIN             AND p20.HISTORICAL = 'F'      t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
        --AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)             AND NOT EXISTS (            OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
     SELECT  1                     FROM   PW001P20 t          AND t.SEQUENCENO < p8.SEQUENCENO
       WHERE  t.PIN = p20.PIN                           )
AND t.HISTORICAL = 'F'                    )
       AND t.DATESTART > p20.DATESTART   ) pass
            ON  pass.PIN = )P01.PIN
       LEFT JOIN PW001OLEDOCSPWCOUNTRY sdocpassc
            ON  sdocpassc.DOCNOCOUNTRYCODE = p20pass.SCANNEDDOCNOISSUE_COUNTRY
       LEFT JOIN PW001C02PW001P0P C02CONTP0P
            ON  p20((P01.RANKPIN = C02CONT.CODE P0P.PIN) AND (P0P.PNUMBER = 'A'))
       LEFT JOIN PW001C32PW001C02 C32CONTp01Rank
            ON  p20p01Rank.CONTRACTTYPECODE = C32CONTp01.code[RANK]
       LEFT JOIN PW001P0TPw001C33 emailc33
            ON  emailc33.PINcode = p01.PINCATHEGORYA
       LEFT JOIN PW001C02 C02
 AND email.TELETYPE = 6        ON  P0P.POSITIONID = C02.CODE
AND email.TELEPRIORITY IS NOT NULL   LEFT JOIN PWORG ORG
      AND NOT EXISTS (   ON  P01.CLIENT = ORG.NUMORGID
       LEFT JOIN PWCOUNTRY PC
  SELECT 1         ON  P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
       FROMLEFT JOIN PW001P01PICT PW001P0TPL
t            ON  P01.PIN = PL.PIN
    WHERE  t.PIN = email.PIN
 LEFT JOIN PWORG PV
            ON  P01.PAYROLLVSL = PV.NUMORGID
       LEFT JOIN ANDPW001P20 t.TELETYPEp20
= email.TELETYPE           ON  p20.PIN = p01.PIN
            AND tp20.TELEPRIORITYHISTORICAL IS= NOT'F'
NULL            AND NOT EXISTS (
            AND (       SELECT 1
                    FROM   PW001P20 t
  t.TELEPRIORITY < email.TELEPRIORITY                WHERE  t.PIN = p20.PIN
                OR t.TELEPRIORITY = email.TELEPRIORITY           AND t.HISTORICAL = 'F'
                           AND t.SEQUENCENODATESTART <> emailp20.SEQUENCENODATESTART
                )
       LEFT JOIN PW001OLEDOCS sdoc
   )         ON  sdoc.DOCNO = p20.SCANNEDDOCNO
   )
       LEFT JOIN PW001P0TPW001C02 mobC02CONT
            ON  mobp20.PINRANK = p01C02CONT.PINCODE
       LEFT JOIN PW001C32 C32CONT
 AND mob.TELETYPE = 3        ON  p20.CONTRACTTYPE = C32CONT.code
AND mob.TELEPRIORITY IS NOT NULL   LEFT JOIN PW001P0T email
      AND NOT EXISTS (   ON  email.PIN = p01.PIN
            AND SELECTemail.TELETYPE 1= 6
            AND email.TELEPRIORITY IS NOT NULL
  FROM   PW001P0T t      AND NOT EXISTS (
           WHERE  t.PIN = mob.PIN     SELECT 1
                    FROM AND t.TELETYPE =PW001P0T mob.TELETYPEt
                       WHERE    AND t.TELEPRIORITYPIN IS= NOTemail.PIN
NULL                            AND (t.TELETYPE = email.TELETYPE
                           AND t.TELEPRIORITY IS NOT NULL
 t.TELEPRIORITY < mob.TELEPRIORITY                        AND (
          OR t.TELEPRIORITY = mob.TELEPRIORITY                      t.TELEPRIORITY < email.TELEPRIORITY
           AND t.SEQUENCENO < mob.SEQUENCENO                     OR t.TELEPRIORITY = email.TELEPRIORITY
       )                 )        LEFT JOIN PWCOUNTRY natAND t.SEQUENCENO < email.SEQUENCENO
         ON  nat.COUNTRYCODE = p01.NATIONALITY        LEFT JOIN PW001P0U p0u          )
                ON)
 p0u.PIN = p01.PIN 	   LEFT JOIN PW001C55PW001P0T c55mob
            ON  p01mob.STOPREASONPIN = c55.CODEp01.PIN
           LEFT JOINAND PW001C04mob.TELETYPE c04= 3
           ON AND c04.CODE = p01.MARITALSTATUS

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