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 p0u.AVPLAN IS NOT NULL THEN 'Available Planning'
WHEN AS 'Full Name',
P01.TITLENAME p0u.AVPLAN IS NULL AND p0u.AVRET IS NOT NULL THEN 'Available to Return'
WHEN p0u.AVPLAN IS NULL AND p0u.AVRET IS NULL ASTHEN 'Title Name',Available'
END P01.FIRSTNAME AS 'First Name', AS [STATUS],
P01.MIDDLENAME P01.ALTERNATIVEPIN AS 'MiddleALTERNATIVE NamePIN',
P01.LASTNAMENAME AS 'LastFull Name',
P01.MAIDENNAMETITLENAME AS 'MaidenTitle Name',
c04P01.[TEXT]FIRSTNAME AS 'MaritalFirst StatusName',
P01.MIDDLENAME CASE WHEN p01.SEX = 'M' THEN 'Male'AS 'Middle Name',
P01.LASTNAME WHEN p01.SEX = 'F' THEN 'Female' ELSEAS 'UndefinedLast Name',
ENDP01.MAIDENNAME AS 'Maiden Name',
CASE AS
Gender, nat.NATIONALITY WHEN p01.SEX = 'M' THEN 'Male'
AS Nationality, ORG.NAME WHEN p01.SEX = 'F' THEN 'Female'
ELSE 'Undefined'
ASEND 'Organization', P01.CLIENT AS NumorgidGender,
P01.EmploymentStartDate,c04.[TEXT] P01.EmploymentEndDate, c55.NAME as 'Termination Reason', p01Rank.[NAME] AS 'Marital Status',
nat.NATIONALITY AS 'Current Rank', -- p01.COSTPLACE as 'Department/Cost Place'AS Nationality,
-- P01.CALLINGNAME AS 'Calling Name',ORG.NAME -- P01.SUFFIXNAME AS 'Suffix Name', -- C02.GROUPNO AS 'Rank SortOrganization',
P01.ADDRESS1 + ' ' + P01.ADDRESS2CLIENT AS 'Address', P01.ADDRESS3 Numorgid,
AS 'City' P01.EmploymentStartDate,
P01.POSTCODE 'Post Code'EmploymentEndDate,
P01.POSTPLACE 'Post Place',c55.NAME PC.COUNTRYNAME AS 'AddressTermination CountryReason',
P01p01Rank.HOMEAIRPORT[NAME] 'Home Airport', -- P01.HOMEAIRPORT2, email.TELENO AS 'Current Rank',
p01.COSTPLACE AS 'E-MAIL', mob.TELENO AS 'Department/Cost Place Code',
c43.[NAME] AS 'Mobile Phone', -- PV.NAME PAYROLLVESSELAS 'Department/Cost Place',
P01.ADDRESS1 + ' ' + P01.BIRTHDATEADDRESS2 AS 'Birth dateAddress',
P01.PERSONALIDNOADDRESS3 AS 'Personal IDCity',
PASSP01.TDNUMBERPOSTCODE 'PassportPost NoCode',
PASS.DATETOP01.POSTPLACE 'Post Place',
PC.COUNTRYNAME AS 'PassportAddress ExpiryCountry',
passc.COUNTRYNAMEP01.HOMEAIRPORT 'Home Airport',
email.TELENO AS 'Passport Country of Issue', AS -- P0P.STARTDATE P0PSTARTDATE'E-MAIL',
-- P0P.ENDDATE P0PENDDATE,mob.TELENO -- P01.PERSONGROUPCODE 'Cost Group', -- c33.Name AS 'CategoryMobile APhone',
P01.BIRTHDATE CASE'Birth date',
P01.PERSONALIDNO WHEN CONTRACTKIND = 0 THEN 'Main Contract' AS 'Personal ID',
WHEN CONTRACTKIND = 1 THENPASS.TDNUMBER 'SubPassport ContractNo',
PASS.DATETO WHEN CONTRACTKIND = 2 THEN 'Ammendment' ELSEAS ''Passport Expiry',
END passc.COUNTRYNAME AS 'Passport Country of Issue',
CASE AS
'Contract Type', C32CONT.Text 'Contract Name', WHEN CONTRACTKIND = 0 THEN C02CONT.NAME 'Contract Rank','Main Contract'
p20.DATESTART 'Contract Start', WHEN CONTRACTKIND = 1 THEN p20.DATEEND'Sub 'Contract End',
p20.DURATION 'Trial period duration', WHEN CONTRACTKIND = 2 THEN p20.TRIALPERIODEND 'Trial period end','Ammendment'
p20.PAYSCALETABLEELSE 'Contract'
Pasycale Table', END p20.PAYSCALECODE 'Contract Pasycale Code', p0u.EMP_PORT 'Employee Portal Access From', CASE AS 'Contract Type',
WHEN p20C32CONT.scanneddocnoText IS NULL THEN NCHAR(9940) + ' Missing''Contract Name',
C02CONT.NAME 'Contract Rank',
WHEN p20.scanvalidityDATESTART IN (1'Contract Start',
0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed' p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
ELSE 'N/A' p20.TRIALPERIODEND 'Trial period ENDend',
p20.PAYSCALETABLE 'Contract Pasycale Table',
p20.PAYSCALECODE 'Contract Pasycale Code',
p0u.EMP_PORT 'Employee ASPortal 'Contract Scan Status'
FROMAccess From',
PW001P01 P01 CASE
LEFT JOIN ( WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
SELECT P8.PIN, WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' P8.tdnumber,Confirmed'
WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' P8.datefrom,
Not Confirmed'
END P8.dateto, AS 'Contract Scan Status'
FROM p8.ISSUEDWHERE,PW001P01 P01
LEFT JOIN (
p8.nativename, SELECT P8.PIN,
p8.ISSUE_COUNTRY, P8.tdnumber,
p8.PASSPORTPRIORITY P8.datefrom,
FROM dbo.PW001P08 P8 P8.dateto,
JOIN dbo.PW001C23 C23 p8.ISSUEDWHERE,
ON P8p8.CODEnativename,
= C23.CODE p8.ISSUE_COUNTRY,
AND C23.OPTIONS LIKE '%T%' p8.PASSPORTPRIORITY
WHERE NOT EXISTS ( FROM dbo.PW001P08 P8
SELECT 1 JOIN dbo.PW001C23 C23
FROM PW001P08 t ON P8.CODE = C23.CODE
JOIN dbo.PW001C23 C23 AND C23.OPTIONS LIKE '%T%'
WHERE NOT EXISTS (
ON t.CODE = C23.CODE SELECT 1
AND C23.OPTIONSFROM LIKE '%T%' PW001P08 t
WHERE t.PIN = p8.PIN JOIN dbo.PW001C23 C23
AND ( ON t.CODE = C23.CODE
t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY AND C23.OPTIONS LIKE '%T%'
WHERE OR t.PASSPORTPRIORITYPIN = p8.PASSPORTPRIORITYPIN
AND (
AND t.SEQUENCENO < p8.SEQUENCENO t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
) ) ) pass OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
ON pass.PIN = P01.PIN LEFT JOIN PWCOUNTRY passc ON passc.COUNTRYCODE = pass.ISSUE_COUNTRY AND t.SEQUENCENO < p8.SEQUENCENO
LEFT JOIN PW001P0P P0P ON ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A')) LEFT JOIN PW001C02 p01Rank )
ON p01Rank.CODE = p01.[RANK] LEFT)
JOIN Pw001C33 c33 ) pass
ON c33.code = p01.CATHEGORYA ON LEFT JOINpass.PIN PW001C02= C02P01.PIN
LEFT JOIN PWCOUNTRY passc
ON P0Ppassc.POSITIONIDCOUNTRYCODE = C02pass.CODEISSUE_COUNTRY
LEFT JOIN PWORGPW001P0P ORGP0P
ON ((P01.CLIENTPIN = ORG.NUMORGIDP0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PWCOUNTRYPW001C02 PCp01Rank
ON P01p01Rank.ADDRESS_COUNTRYCODE = PCp01.COUNTRYCODE[RANK]
LEFT JOIN PW001P01PICTPw001C33 PLc33
ON P01c33.PINcode = PLp01.PINCATHEGORYA
LEFT JOIN PWORGPW001C02 PVC02
ON P01P0P.PAYROLLVSLPOSITIONID = PVC02.NUMORGIDCODE
LEFT JOIN PW001P20PWORG p20ORG
ON p20P01.PINCLIENT = p01.PINORG.NUMORGID
LEFT JOIN PWCOUNTRY PC
ANDON p20P01.HISTORICALADDRESS_COUNTRY = 'F'PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL
--AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED) ON P01.PIN = PL.PIN
LEFT JOIN PWORG PV
AND NOT EXISTS ( ON P01.PAYROLLVSL = PV.NUMORGID
LEFT SELECTJOIN 1PW001P20 p20
ON p20.PIN = p01.PIN
FROM PW001P20 t AND p20.HISTORICAL = 'F'
WHERE AND t.PINNOT =EXISTS p20.PIN(
SELECT 1
AND t.HISTORICAL = 'F' FROM PW001P20 t
AND t.DATESTART > p20.DATESTART WHERE t.PIN = p20.PIN
) LEFT JOIN PW001OLEDOCS sdoc AND t.HISTORICAL = ON'F'
sdoc.DOCNO = p20.SCANNEDDOCNO LEFT JOIN PW001C02 C02CONT ONAND t.DATESTART > p20.RANKDATESTART
= C02CONT.CODE LEFT JOIN PW001C32 C32CONT )
LEFT ONJOIN PW001OLEDOCS p20.CONTRACTTYPEsdoc
= C32CONT.code LEFT JOIN PW001P0T emailON sdoc.DOCNO = p20.SCANNEDDOCNO
LEFT ONJOIN PW001C02 email.PINC02CONT
= p01.PIN ON AND emailp20.TELETYPERANK = 6C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
AND email.TELEPRIORITY IS NOT NULL ON p20.CONTRACTTYPE = C32CONT.code
AND NOT EXISTS ( LEFT JOIN PW001P0T email
ON SELECTemail.PIN 1= p01.PIN
AND email.TELETYPE = 6
FROM PW001P0T t AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS WHERE(
t.PIN = email.PIN SELECT 1
AND t.TELETYPE = email.TELETYPE FROM PW001P0T t
AND t.TELEPRIORITY IS NOT NULL WHERE t.PIN = email.PIN
AND ( AND t.TELETYPE = email.TELETYPE
AND t.TELEPRIORITY IS NOT NULL
AND (
t.TELEPRIORITY < email.TELEPRIORITY
OR t.TELEPRIORITY = email.TELEPRIORITY
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 |