CREATE VIEW dbo.VIEWIDPW001SRV12
AS

SELECT P01.PIN                               PIN,
       P01.ALTERNATIVEPINCASE 
            WHEN c12.[TEXT] IS NOT ASNULL 'ALTERNATIVE PIN',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',
       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 'OrganizationMaiden Name',
       --	C02.GROUPNO AS 'Rank Sort',CASE 
         P01.CLIENT   WHEN p01.SEX = 'M' THEN 'Male'
            WHEN p01.SEX = 'F' THEN 'Female'
 Numorgid,        P01.EmploymentStartDate,   ELSE 'Undefined'
   P01.EmploymentEndDate,    END    nat.NATIONALITY                    AS Nationality,        --	PV.NAME PAYROLLVESSELAS Gender,
        P01.BIRTHDATE 'Birth date',c04.[TEXT]          P01.PERSONALIDNO                   AS 'PersonalMarital IDStatus',
       PASSnat.TDNUMBERNATIONALITY   'Passport No',        PASS.DATETO        AS Nationality,
       ORG.NAME       AS 'Passport Expiry',        passc.COUNTRYNAME          AS 'Organization',
      AS 'PassportP01.CLIENT Country of Issue',        --	P0P.STARTDATE P0PSTARTDATE,        --	P0P.ENDDATE P0PENDDATE,        --	P01.PERSONGROUPCODE 'Cost Group'Numorgid,
       --	c33.Name AS 'Category A'P01.EmploymentStartDate,
       P01.ADDRESS1EmploymentEndDate,
+ ' ' + P01.ADDRESS2  AS 'Address',
   c55.NAME     P01.ADDRESS3                       AS 'CityTermination Reason',
       P01p01Rank.POSTCODE 'Post Code',[NAME]                     P01.POSTPLACEAS 'PostCurrent PlaceRank',
       PCp01.COUNTRYNAMECOSTPLACE                      AS 'Address CountryDepartment/Cost Place Code',
       P01c43.HOMEAIRPORT[NAME] 'Home Airport',        --	P01.HOMEAIRPORT2,          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 = 0P01.HOMEAIRPORT THEN 'MainHome ContractAirport',
       email.TELENO     WHEN CONTRACTKIND = 1 THEN 'Sub Contract'             WHEN CONTRACTKIND = 2 THEN 'Ammendment'AS 'E-MAIL',
       mob.TELENO          ELSE ''        END      AS 'Mobile Phone',
       P01.BIRTHDATE 'Birth date',
       P01.PERSONALIDNO      AS    'Contract Type',        C32CONT.TextAS 'ContractPersonal NameID',
       C02CONTPASS.NAMETDNUMBER 'ContractPassport RankNo',
       p20PASS.DATESTARTDATETO 'Contract Start',        p20.DATEEND 'Contract End',        p20.DURATION 'Trial period duration',
       p20.TRIALPERIODEND 'Trial period end AS 'Passport Expiry',
       p20passc.PAYSCALETABLECOUNTRYNAME 'Contract Pasycale Table',        p20.PAYSCALECODE 'Contract Pasycale Code',        p0u.EMP_PORTAS 'EmployeePassport Country Accessof FromIssue',
       CASE 
            WHEN p20.scanneddocnoCONTRACTKIND IS= NULL0 THEN NCHAR(9940) + 'Main MissingContract'
            WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed CONTRACTKIND = 1 THEN '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 'Contract Start',
SELECT P8.PIN,      p20.DATEEND 'Contract End',
       p20.DURATION 'Trial period duration',
     P8.tdnumber,
   p20.TRIALPERIODEND 'Trial period end',
       p20.PAYSCALETABLE 'Contract Pasycale Table',
       p20.PAYSCALECODE 'Contract Pasycale P8.datefromCode',
       p0u.EMP_PORT 'Employee Portal Access From',
       CASE 
   P8.dateto,         WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
       p8.ISSUEDWHERE,     WHEN 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'
  p8.ISSUE_COUNTRY,     END                   p8.PASSPORTPRIORITY             AS 'Contract Scan Status',
FROM   dbo.PW001P08 P8   (
           SELECT CONVERT(
       JOIN dbo.PW001C23 C23             VARCHAR(999),
               ON  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              )
           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  P01.ADDRESS_COUNTRY =AND PCC23.COUNTRYCODEOPTIONS LIKE '%T%'
     LEFT JOIN PW001P01PICT PL             ON  P01.PIN = PL.PIN  WHERE  t.PIN = p8.PIN
 LEFT JOIN PWORG PV             ON  P01.PAYROLLVSL = PV.NUMORGID        LEFT JOIN PW001P20 p20  AND (
         ON  p20.PIN = p01.PIN             AND p20.HISTORICAL = 'F'                 --AND p20t.DATESTARTPASSPORTPRIORITY BETWEEN< p03planp8.DATEFROMPASSPORTPRIORITY
AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)             AND NOT EXISTS (                     SELECT 1  OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
                FROM   PW001P20 t                     WHERE AND t.PINSEQUENCENO =< p20p8.PINSEQUENCENO
                           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)
                )