CREATE VIEW dbo.PW001SRV12
AS
SELECT P01.PIN PIN,
(CASE
SELECT CONVERT( WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
WHEN p03plan.PIN IS VARCHAR(999),NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] + ' ' REPLACE(
+ CONVERT(VARCHAR, p03plan.DATEFROM, 1)
WHEN p0u2.AVPLAN IS NOT NULL THEN REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
'Available Planning'
WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NOT CHAR(10),NULL THEN 'Available to Return'
WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NULL THEN 'Available'
END ) AS [STATUS],
) P01.ALTERNATIVEPIN FROM PW001P0N Personal AS 'ALTERNATIVE PIN',
WHERE Personal.PIN = p01pass.PINnativename AND Personal.FIELDNO = 'p17'AS 'Native Name',
P01.NAME ) AS 'Full Name',
ASP01.TITLENAME 'Notes - Personal Details', CASE AS 'Title Name',
WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]P01.FIRSTNAME WHEN p03plan.PIN IS NOT NULL THEN 'Nearest plannedAS to'First Name',
+ c12plan.[TEXT] + ' ' + CONVERT(VARCHAR, p03plan.DATEFROM, 1)P01.MIDDLENAME WHEN p0u2.AVPLAN IS NOT NULL THENAS 'AvailableMiddle Planning'
Name',
P01.LASTNAME WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NOT NULL THEN 'Available to Return'
AS 'Last Name',
P01.MAIDENNAME WHEN p0u2.AVPLAN IS NULL AND p0u2.AVRET IS NULL THEN 'Available' AS 'Maiden Name',
END CASE
WHEN p01.SEX = 'M' THEN 'Male'
AS [STATUS], WHEN p01.SEX = 'F' P01.ALTERNATIVEPINTHEN 'Female'
ELSE 'Undefined'
AS 'ALTERNATIVE PIN', END P01.NAME AS 'Full Name'Gender,
P01.TITLENAMEc04.[TEXT] AS 'TitleMarital NameStatus',
P01nat.FIRSTNAMENATIONALITY AS Nationality,
AS 'First Name',
ORG.NAME P01.MIDDLENAME AS 'Middle NameOrganization',
P01.LASTNAMECLIENT AS 'Last Name'Numorgid,
P01.MAIDENNAMEEmploymentStartDate,
P01.EmploymentEndDate,
c55.NAME AS 'Maiden Name', CASE WHEN p01.SEX =AS 'M'Termination THEN Reason'Male',
WHEN p01.SEX = 'F' THEN 'Female'
p01Rank.[NAME] ELSE 'Undefined' AS 'Current ENDRank',
p01.COSTPLACE AS 'Department/Cost ASPlace GenderCode',
c04c43.[TEXTNAME] AS 'MaritalDepartment/Cost StatusPlace',
nat.NATIONALITY P01.ADDRESS1 + ' ' + P01.ADDRESS2 AS 'Address',
P01.ADDRESS3 AS Nationality, ORG.NAME AS 'City',
P01.POSTCODE 'Post Code',
ASP01.POSTPLACE 'OrganizationPost Place',
P01.CLIENT PC.COUNTRYNAME AS 'Address NumorgidCountry',
P01.EmploymentStartDateHOMEAIRPORT 'Home Airport',
P01email.EmploymentEndDate,TELENO c55.NAME AS 'E-MAIL',
mob.TELENO AS 'Termination Reason', p01Rank.[NAME] AS 'Mobile Phone',
ASP01.BIRTHDATE 'CurrentBirth Rankdate',
p01P01.COSTPLACEPERSONALIDNO AS 'Personal AS 'Department/Cost Place CodeID',
PASS.TDNUMBER 'Passport No',
c43PASS.[NAME]DATETO AS 'Department/CostPassport PlaceExpiry',
P01.ADDRESS1 + ' ' + P01.ADDRESS2passc.COUNTRYNAME AS 'AddressPassport Country of Issue',
P01.ADDRESS3CASE
WHEN CONTRACTKIND = 0 THEN 'Main Contract'
AS 'City', WHEN CONTRACTKIND = 1 P01.POSTCODETHEN 'PostSub CodeContract',
P01.POSTPLACE 'Post Place', WHEN CONTRACTKIND = 2 PC.COUNTRYNAMETHEN 'Ammendment'
ELSE ''
AS 'AddressEND Country', P01.HOMEAIRPORT 'Home Airport', email.TELENO AS 'Contract Type',
ASC32CONT.Text 'E-MAILContract Name',
mob.TELENOC02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p20.DATEEND 'Contract End',
p20.DURATION AS'Trial 'Mobileperiod Phoneduration',
P01p20.BIRTHDATETRIALPERIODEND 'BirthTrial period dateend',
P01.PERSONALIDNO p20.PAYSCALETABLE 'Contract Pasycale Table',
p20.PAYSCALECODE 'Contract Pasycale Code',
AS 'Personal ID',
p0u.EMP_PORT 'Employee Portal Access From',
PASS.TDNUMBER 'Passport No', CASE
PASS.DATETO WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
AS 'Passport Expiry', WHEN p20.scanvalidity IN (1) AND passc.COUNTRYNAME p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
AS 'Passport CountryWHEN of Issue',
CASE
p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
END WHEN CONTRACTKIND = 0 THEN 'Main Contract' WHEN CONTRACTKIND = 1 THEN 'Sub Contract' AS 'Contract Scan Status',
WHEN CONTRACTKIND = 2(
THEN 'Ammendment' SELECT CONVERT(
ELSE '' END VARCHAR(999),
AS 'Contract Type', REPLACE(
C32CONT.Text 'Contract Name', C02CONT.NAME 'Contract Rank', p20.DATESTART 'Contract Start',REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
p20.DATEEND 'Contract End', p20.DURATION 'Trial period duration', CHAR(10),
p20.TRIALPERIODEND 'Trial period end', p20.PAYSCALETABLE 'Contract Pasycale Table', p20.PAYSCALECODE 'Contract'
Pasycale Code', p0u.EMP_PORT 'Employee Portal Access From', CASE)
WHEN p20.scanneddocno IS NULL THEN NCHAR(9940)
+ ' Missing' FROM PW001P0N WHENPersonal
p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + 'WHERE Confirmed' Personal.PIN = p01.PIN
WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'AND Personal.FIELDNO = 'p17'
) END AS 'ContractNotes - ScanPersonal StatusDetails'
FROM PW001P01 P01
LEFT JOIN (
SELECT P8.PIN,
P8.tdnumber,
P8.datefrom,
P8.dateto,
p8.ISSUEDWHERE,
p8.nativename,
p8.ISSUE_COUNTRY,
p8.PASSPORTPRIORITY
FROM dbo.PW001P08 P8
JOIN dbo.PW001C23 C23
ON P8.CODE = C23.CODE
AND C23.OPTIONS LIKE '%T%'
WHERE NOT EXISTS (
SELECT 1
FROM PW001P08 t
JOIN dbo.PW001C23 C23
ON t.CODE = C23.CODE
AND C23.OPTIONS LIKE '%T%'
WHERE t.PIN = p8.PIN
AND (
t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
AND t.SEQUENCENO < p8.SEQUENCENO
)
)
) pass
ON pass.PIN = P01.PIN
LEFT JOIN PWCOUNTRY passc
ON passc.COUNTRYCODE = pass.ISSUE_COUNTRY
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
ON c33.code = p01.CATHEGORYA
LEFT JOIN PW001C02 C02
ON P0P.POSITIONID = C02.CODE
LEFT JOIN PWORG ORG
ON P01.CLIENT = ORG.NUMORGID
LEFT JOIN PWCOUNTRY PC
ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL
ON P01.PIN = PL.PIN
LEFT JOIN PWORG PV
ON P01.PAYROLLVSL = PV.NUMORGID
LEFT JOIN PW001P20 p20
ON p20.PIN = p01.PIN
AND p20.HISTORICAL = 'F'
AND NOT EXISTS (
SELECT 1
FROM PW001P20 t
WHERE t.PIN = p20.PIN
AND t.HISTORICAL = 'F'
AND t.DATESTART > p20.DATESTART
)
LEFT JOIN PW001OLEDOCS sdoc
ON sdoc.DOCNO = p20.SCANNEDDOCNO
LEFT JOIN PW001C02 C02CONT
ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
ON p20.CONTRACTTYPE = C32CONT.code
LEFT JOIN PW001P0T email
ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM PW001P0T t
WHERE t.PIN = email.PIN
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
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)
)
|
