CREATE VIEW dbo.PW001SRV12
AS
SELECT P01.PIN PIN,
CASE(
SELECT CONVERT(
WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT] WHEN p0u.AVPLAN IS NOTVARCHAR(999),
NULL THEN 'Available Planning' WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NOTREPLACE(
NULL THEN 'Available to Return' WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NULL THEN 'Available'
REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
END CHAR(10),
AS [STATUS], P01.ALTERNATIVEPIN ''
AS 'ALTERNATIVE PIN', P01.NAME )
)
AS 'Full Name', FROM P01.TITLENAMEPW001P0N Personal
WHERE Personal.PIN = p01.PIN
AS 'Title Name', P01.FIRSTNAME AND Personal.FIELDNO = 'p17'
) AS 'First Name', P01.MIDDLENAME AS 'Notes - Personal AS Details'Middle,
Name', CASE P01.LASTNAME
WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
AS 'Last Name', WHEN P01p03plan.MAIDENNAMEPIN IS NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] + ' ' + CONVERT(VARCHAR, p03plan.DATEFROM, 1)
AS 'Maiden Name', WHEN CASEp0u2.AVPLAN IS NOT NULL THEN 'Available Planning'
WHEN p01.SEX = 'M' THEN 'Male'
WHEN p01.SEX = 'F' THEN 'Female'WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NOT NULL THEN 'Available to Return'
WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NULL ELSETHEN 'UndefinedAvailable'
END AS Gender[STATUS],
c04P01.[TEXT]ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
AS 'Marital Status', P01.NAME nat.NATIONALITY AS 'Full NationalityName',
ORGP01.NAMETITLENAME AS 'OrganizationTitle Name',
P01.CLIENTFIRSTNAME AS 'First Name',
Numorgid, P01.MIDDLENAME P01.EmploymentStartDate, P01.EmploymentEndDate, c55.NAME AS 'Middle Name',
P01.LASTNAME AS 'Termination Reason AS 'Last Name',
p01RankP01.[NAME]MAIDENNAME AS 'CurrentMaiden RankName',
p01.COSTPLACECASE
WHEN p01.SEX = 'M' THEN 'Male'
AS 'Department/Cost Place Code', WHEN c43p01.[NAME]SEX = 'F' THEN 'Female'
ELSE 'Undefined'
AS 'Department/Cost Place',END P01.ADDRESS1 + ' ' + P01.ADDRESS2 AS 'Address', P01.ADDRESS3 AS Gender,
c04.[TEXT] AS 'City',
P01.POSTCODE 'Post Code', P01.POSTPLACEAS 'PostMarital PlaceStatus',
PCnat.COUNTRYNAME NATIONALITY AS 'Address Country'Nationality,
P01.HOMEAIRPORT 'Home Airport',
ORG.NAME email.TELENO AS 'E-MAILOrganization',
mobP01.TELENOCLIENT AS 'Mobile Phone'Numorgid,
P01.BIRTHDATE 'Birth date'EmploymentStartDate,
P01.PERSONALIDNOEmploymentEndDate,
c55.NAME AS 'Personal ID', PASS.TDNUMBERAS 'PassportTermination NoReason',
PASS.DATETO p01Rank.[NAME] AS 'PassportCurrent ExpiryRank',
passc.COUNTRYNAMEp01.COSTPLACE AS 'PassportDepartment/Cost CountryPlace of IssueCode',
CASEc43.[NAME] WHEN CONTRACTKIND = 0 THEN 'Main Contract' AS 'Department/Cost Place',
WHEN CONTRACTKIND =P01.ADDRESS1 1+ THEN' 'Sub Contract' + P01.ADDRESS2 AS 'Address',
P01.ADDRESS3 WHEN CONTRACTKIND = 2 THEN 'Ammendment' ELSEAS 'City',
ENDP01.POSTCODE 'Post Code',
P01.POSTPLACE 'Post Place',
PC.COUNTRYNAME AS 'Contract Type', AS C32CONT.Text'Address Country'Contract Name',
C02CONTP01.NAMEHOMEAIRPORT 'ContractHome RankAirport',
p20email.DATESTARTTELENO 'Contract Start', p20.DATEEND 'Contract End', p20.DURATION 'Trial period durationAS 'E-MAIL',
p20.TRIALPERIODEND 'Trial period end',mob.TELENO p20.PAYSCALETABLE 'Contract Pasycale Table', p20.PAYSCALECODEAS 'ContractMobile Pasycale CodePhone',
p0uP01.EMP_PORTBIRTHDATE 'Employee Portal Access FromBirth date',
CASEP01.PERSONALIDNO WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + AS 'Personal MissingID',
PASS.TDNUMBER 'Passport No',
WHEN p20.scanvalidity IN (1) AND p20PASS.scanneddocnoDATETO IS NOT NULL THEN NCHAR(10004) + ' Confirmed' WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'AS 'Passport Expiry',
passc.COUNTRYNAME END AS 'Passport Country of Issue',
CASE
AS 'Contract ScanWHEN Status'CONTRACTKIND FROM= 0 THEN PW001P01'Main P01Contract'
LEFT JOIN ( WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
SELECT P8.PIN, WHEN CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
P8.tdnumber, END P8.datefrom, AS 'Contract Type',
P8.dateto C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p8.ISSUEDWHERE, p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
p8p20.nativename,TRIALPERIODEND 'Trial period end',
p20.PAYSCALETABLE 'Contract Pasycale Table',
p20.PAYSCALECODE p8.ISSUE_COUNTRY'Contract Pasycale Code',
p0u.EMP_PORT 'Employee Portal Access From',
CASE
p8.PASSPORTPRIORITY WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + FROM' Missing'
dbo.PW001P08 P8 WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
JOIN dbo.PW001C23 C23 WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
ON P8.CODE =END C23.CODE AND C23.OPTIONS LIKEAS '%T%'Contract Scan Status'
FROM PW001P01 P01
WHERE NOT EXISTSLEFT JOIN (
SELECT P8.PIN,
SELECT 1 P8.tdnumber,
FROM PW001P08 t P8.datefrom,
P8.dateto,
JOIN dbo.PW001C23 C23 p8.ISSUEDWHERE,
ONp8.nativename,
t.CODE = C23.CODE p8.ISSUE_COUNTRY,
AND C23.OPTIONS LIKE '%T%' p8.PASSPORTPRIORITY
FROM dbo.PW001P08 P8
WHERE t.PIN = p8.PIN JOIN dbo.PW001C23 C23
AND ( ON P8.CODE = C23.CODE
AND tC23.PASSPORTPRIORITYOPTIONS < p8.PASSPORTPRIORITYLIKE '%T%'
WHERE NOT EXISTS (
OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY SELECT 1
FROM PW001P08 t
AND t.SEQUENCENO < p8.SEQUENCENO JOIN dbo.PW001C23 C23
) ) ON t.CODE = C23.CODE
) pass ON pass.PIN = P01.PIN LEFT JOIN PWCOUNTRY passc AND C23.OPTIONS ON passc.COUNTRYCODE = pass.ISSUE_COUNTRYLIKE '%T%'
LEFT JOIN PW001P0P P0P ONWHERE ((P01t.PIN = P0Pp8.PIN)
AND (P0P.PNUMBER = 'A')) LEFT JOIN PW001C02 p01Rank ON p01Rank.CODE = p01.[RANK] AND (
LEFT JOIN Pw001C33 c33 ON c33.code = p01.CATHEGORYA LEFT JOIN PW001C02 C02 ON P0P.POSITIONID = C02.CODEt.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
LEFT JOIN PWORG ORG ON P01.CLIENT = ORG.NUMORGID LEFT JOIN PWCOUNTRY PC OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE LEFT JOIN PW001P01PICT PL ON P01.PIN = PL.PIN AND t.SEQUENCENO < LEFTp8.SEQUENCENO
JOIN PWORG PV ON P01.PAYROLLVSL = PV.NUMORGID LEFT JOIN PW001P20 p20 )
ON p20.PIN = p01.PIN AND p20.HISTORICAL = 'F')
AND) NOTpass
EXISTS ( ON pass.PIN = P01.PIN
SELECT 1 LEFT JOIN PWCOUNTRY passc
ON passc.COUNTRYCODE = FROMpass.ISSUE_COUNTRY
PW001P20 t LEFT JOIN PW001P0P P0P
ON WHERE t((P01.PIN = p20P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 p01Rank
ON AND tp01Rank.HISTORICALCODE = 'F'p01.[RANK]
LEFT JOIN Pw001C33 c33
ON c33.code = AND tp01.DATESTARTCATHEGORYA
> p20.DATESTART LEFT JOIN PW001C02 C02
ON P0P.POSITIONID = )C02.CODE
LEFT JOIN PW001OLEDOCSPWORG sdocORG
ON sdocP01.DOCNOCLIENT = p20ORG.SCANNEDDOCNONUMORGID
LEFT JOIN PW001C02PWCOUNTRY C02CONTPC
ON p20P01.RANKADDRESS_COUNTRY = C02CONTPC.CODECOUNTRYCODE
LEFT JOIN PW001C32PW001P01PICT C32CONTPL
ON p20P01.CONTRACTTYPEPIN = C32CONTPL.codePIN
LEFT JOIN PW001P0TPWORG emailPV
ON emailP01.PINPAYROLLVSL = p01PV.PINNUMORGID
LEFT JOIN PW001P20 p20
AND email.TELETYPE ON p20.PIN = 6p01.PIN
AND emailp20.TELEPRIORITYHISTORICAL IS NOT NULL= 'F'
AND NOT EXISTS (
SELECT 1
FROM PW001P0TPW001P20 t
WHERE t.PIN = emailp20.PIN
AND t.TELETYPEHISTORICAL = email.TELETYPE'F'
AND t.TELEPRIORITYDATESTART IS> NOTp20.DATESTART
NULL )
LEFT JOIN PW001OLEDOCS sdoc
AND ( ON sdoc.DOCNO = p20.SCANNEDDOCNO
LEFT JOIN PW001C02 C02CONT
t.TELEPRIORITY < email.TELEPRIORITY ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
ON p20.CONTRACTTYPE = C32CONT.code
OR t.TELEPRIORITY = email.TELEPRIORITY LEFT JOIN PW001P0T email
ON email.PIN = p01.PIN
AND temail.SEQUENCENOTELETYPE = <6
email.SEQUENCENO AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS )(
) SELECT 1
LEFT JOIN PW001P0T mob ON mob.PINFROM = p01.PIN PW001P0T t
AND mob.TELETYPE = 3 WHERE t.PIN = email.PIN
AND mob.TELEPRIORITY IS NOT NULL AND NOT EXISTS ( AND t.TELETYPE = email.TELETYPE
SELECT 1 AND t.TELEPRIORITY IS NOT NULL
FROM PW001P0T t WHERE AND t.PIN(
= mob.PIN AND t.TELETYPE = mob.TELETYPE t.TELEPRIORITY < email.TELEPRIORITY
AND t.TELEPRIORITY IS NOT NULL OR t.TELEPRIORITY = email.TELEPRIORITY
AND ( AND t.SEQUENCENO < email.SEQUENCENO
t.TELEPRIORITY < mob.TELEPRIORITY )
)
OR t.TELEPRIORITY = mob.TELEPRIORITY LEFT JOIN PW001P0T mob
ON mob.PIN = p01.PIN
AND t.SEQUENCENO < mob.SEQUENCENOTELETYPE = 3
AND mob.TELEPRIORITY IS NOT NULL
)AND NOT EXISTS (
) SELECT LEFT1
JOIN PWCOUNTRY nat ON nat.COUNTRYCODE = p01.NATIONALITY FROM PW001P0T t
LEFT JOIN PW001P0U p0u ONWHERE p0ut.PIN = p01mob.PIN
LEFT JOIN PW001C55 c55 ON AND p01t.STOPREASONTELETYPE = c55mob.CODETELETYPE
LEFT JOIN PW001C43 c43 ON c43.CODE =AND p01t.COSTPLACETELEPRIORITY IS NOT NULL
LEFT JOIN PW001C04 c04 ON c04.CODE = p01.MARITALSTATUS AND (
LEFT JOIN dbo.PW001P03 P03cur ON P01.PIN = P03cur.PIN t.TELEPRIORITY AND< (P03cur.DATETO IS NULL OR P03cur.DATETO >= GETDATE())mob.TELEPRIORITY
AND P03cur.DATEFROM <= GETDATE() OR t.TELEPRIORITY AND= P03cur.PLANNED <> 'Y'mob.TELEPRIORITY
LEFT JOIN PW001C12 c12 ON AND c12t.CODESEQUENCENO =< p03cur.CODE |