CREATE VIEW dbo.PW001SRV12
AS
SELECT P01.PIN PIN,
CASE
WHEN c12.[TEXT] IS NOT NULL THEN c12.[TEXT]
WHEN p0up03plan.PIN IS NOT NULL THEN 'Nearest planned to ' + c12plan.[TEXT] + ' ' + CONVERT(VARCHAR, p03plan.DATEFROM, 1)
WHEN p0u2.AVPLAN IS NOT NULL THEN 'Available Planning'
WHEN p0up0u2.AVPLAN IS NULL AND p0up0u2.AVRET IS NOT NULL THEN 'Available to Return'
WHEN p0up0u2.AVPLAN IS NULL AND p0up0u2.AVRET IS NULL THEN 'Available'
END AS [STATUS],
P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
P01pass.NAMEnativename AS 'Native Name',
P01.NAME AS 'Full Name',
P01.TITLENAME AS 'Title Name',
P01.FIRSTNAME AS 'First Name',
P01.MIDDLENAME AS 'Middle Name',
P01.LASTNAME AS 'Last Name',
P01.MAIDENNAME AS 'Maiden Name',
CASE
WHEN p01.SEX = 'M' THEN 'Male'
WHEN p01.SEX = 'F' THEN 'Female'
ELSE 'Undefined'
END AS Gender,
c04.[TEXT] AS 'Marital Status',
nat.NATIONALITY AS Nationality,
ORG.NAME AS 'Organization',
P01.CLIENT Numorgid,
P01.EmploymentStartDate,
P01.EmploymentEndDate,
c55.NAME AS 'Termination Reason',
p01Rank.[NAME] AS 'Current Rank',
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place',
P01.ADDRESS1 + ' ' + P01.ADDRESS2 AS 'Address',
P01.ADDRESS3 AS 'City',
P01.POSTCODE 'Post Code',
P01.POSTPLACE 'Post Place',
PC.COUNTRYNAME AS 'Address Country',
P01.HOMEAIRPORT 'Home Airport',
email.TELENO AS 'E-MAIL',
mob.TELENO AS 'Mobile Phone',
P01.BIRTHDATE 'Birth date',
P01.PERSONALIDNO AS 'Personal ID',
PASS.TDNUMBER 'Passport No',
PASS.DATETO AS 'Passport Expiry',
passc.COUNTRYNAME AS 'Passport Country of Issue',
CASE
WHEN CONTRACTKIND = 0 THEN 'Main Contract'
WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
WHEN CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END AS 'Contract Type',
C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
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'
WHEN p20.scanvalidity IN (1) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
WHEN p20.scanvalidity IN (0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10006) + ' Not Confirmed'
END AS 'Contract Scan Status'
FROM PW001P01 P01,
LEFT JOIN (
SELECT P8.PIN,CONVERT(
P8.tdnumberVARCHAR(999),
REPLACE(
P8.datefrom, P8.dateto, REPLACE(dbo.ad_RtfToText(Personal.NOTES), CHAR(13), ''),
p8.ISSUEDWHERE, CHAR(10),
p8.nativename, ''
p8.ISSUE_COUNTRY, )
p8.PASSPORTPRIORITY )
FROM dbo.PW001P08PW001P0N P8Personal
WHERE Personal.PIN = p01.PIN
JOIN dbo.PW001C23 C23 AND Personal.FIELDNO = 'p17'
) ON P8.CODE = C23.CODE AS 'Notes - Personal Details'
FROM ANDPW001P01 C23.OPTIONSP01
LIKE '%T%' LEFT JOIN (
WHERE NOT EXISTS ( SELECT P8.PIN,
SELECT 1P8.tdnumber,
P8.datefrom,
FROM PW001P08 t P8.dateto,
JOIN dbo.PW001C23 C23 p8.ISSUEDWHERE,
p8.nativename,
ON t.CODE = C23.CODE p8.ISSUE_COUNTRY,
p8.PASSPORTPRIORITY
AND C23.OPTIONS LIKE '%T%' FROM dbo.PW001P08 P8
WHERE t.PIN = p8.PIN JOIN dbo.PW001C23 C23
ON ANDP8.CODE (= C23.CODE
AND C23.OPTIONS LIKE '%T%'
t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY WHERE NOT EXISTS (
SELECT 1
OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY FROM PW001P08 t
AND t.SEQUENCENO < p8.SEQUENCENO JOIN dbo.PW001C23 C23
) ON t.CODE = C23.CODE
) ) pass ON pass.PIN = P01.PIN AND C23.OPTIONS LIKE LEFT'%T%'
JOIN PWCOUNTRY passc ON passc.COUNTRYCODE = pass.ISSUE_COUNTRY LEFTWHERE JOIN PW001P0Pt.PIN P0P= p8.PIN
ON ((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A')) LEFT JOIN PW001C02 p01Rank AND (
ON p01Rank.CODE = p01.[RANK] LEFT JOIN Pw001C33 c33 ON c33.code = p01.CATHEGORYA t.PASSPORTPRIORITY < p8.PASSPORTPRIORITY
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID = C02.CODE LEFT JOIN PWORG ORG OR t.PASSPORTPRIORITY = p8.PASSPORTPRIORITY
ON P01.CLIENT = ORG.NUMORGID LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE LEFT JOIN PW001P01PICTAND PLt.SEQUENCENO < p8.SEQUENCENO
ON P01.PIN = PL.PIN LEFT JOIN PWORG PV ON )
P01.PAYROLLVSL = PV.NUMORGID LEFT JOIN PW001P20 p20 )
ON p20.PIN = p01.PIN ) pass
AND p20.HISTORICAL = 'F' ON pass.PIN = P01.PIN
AND NOT EXISTS (LEFT JOIN PWCOUNTRY passc
ON passc.COUNTRYCODE = pass.ISSUE_COUNTRY
SELECT 1 LEFT JOIN PW001P0P P0P
FROM ON PW001P20 t
((P01.PIN = P0P.PIN) AND (P0P.PNUMBER = 'A'))
LEFT JOIN PW001C02 p01Rank
WHERE t.PIN = p20.PIN ON p01Rank.CODE = p01.[RANK]
LEFT JOIN Pw001C33 c33
AND t.HISTORICAL = 'F' ON c33.code = p01.CATHEGORYA
LEFT JOIN PW001C02 C02
AND ON tP0P.DATESTARTPOSITIONID >= p20.DATESTARTC02.CODE
LEFT JOIN PWORG ORG
ON P01.CLIENT = )ORG.NUMORGID
LEFT JOIN PW001OLEDOCSPWCOUNTRY sdocPC
ON sdocP01.DOCNOADDRESS_COUNTRY = p20PC.SCANNEDDOCNOCOUNTRYCODE
LEFT JOIN PW001C02PW001P01PICT C02CONTPL
ON p20P01.RANKPIN = C02CONTPL.CODEPIN
LEFT JOIN PW001C32PWORG C32CONTPV
ON p20P01.CONTRACTTYPEPAYROLLVSL = C32CONTPV.codeNUMORGID
LEFT JOIN PW001P0TPW001P20 emailp20
ON emailp20.PIN = p01.PIN
AND emailp20.TELETYPEHISTORICAL = 6
AND email.TELEPRIORITY 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.TELEPRIORITY IS NOT NULLDATESTART > p20.DATESTART
)
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
ORLEFT t.TELEPRIORITYJOIN =PW001P0T email.TELEPRIORITY
ON email.PIN = p01.PIN
AND email.TELETYPE = 6
AND t.SEQUENCENO < email.SEQUENCENO AND email.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
) SELECT )1
LEFT JOIN PW001P0T mob FROM ONPW001P0T t
mob.PIN = p01.PIN 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 AND (
WHERE t.PIN = mob.PIN AND t.TELETYPETELEPRIORITY =< mobemail.TELETYPE
TELEPRIORITY
AND t.TELEPRIORITY IS NOT NULL OR t.TELEPRIORITY = email.TELEPRIORITY
AND ( AND t.SEQUENCENO < email.SEQUENCENO
t.TELEPRIORITY < mob.TELEPRIORITY )
)
ORLEFT t.TELEPRIORITYJOIN =PW001P0T mob.TELEPRIORITY
ON mob.PIN = p01.PIN
AND mob.TELETYPE = 3
AND t.SEQUENCENO < mob.SEQUENCENO AND mob.TELEPRIORITY IS NOT NULL
AND NOT EXISTS (
) SELECT )1
LEFT JOIN PWCOUNTRY nat FROM ON nat.COUNTRYCODE = p01.NATIONALITYPW001P0T t
LEFT JOIN PW001P0U p0u WHERE ON 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.CODEmob.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)
)
|
