CREATE VIEW dbo.VIEWID
AS
SELECT P01.PIN PIN,
P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
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,
p01Rank.[NAME] AS 'Current Rank',
-- P01.CALLINGNAME AS 'Calling Name',
-- P01.SUFFIXNAME AS 'Suffix Name',
ORG.NAME AS 'Organization',
-- C02.GROUPNO AS 'Rank Sort',
P01.CLIENT Numorgid,
P01.EmploymentStartDate,
P01.EmploymentEndDate,
nat.NATIONALITY AS Nationality,
-- PV.NAME PAYROLLVESSEL,
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',
-- P0P.STARTDATE P0PSTARTDATE,
-- P0P.ENDDATE P0PENDDATE,
-- P01.PERSONGROUPCODE 'Cost Group',
-- c33.Name AS 'Category A',
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',
-- P01.HOMEAIRPORT2,
email.TELENO AS 'E-MAIL',
mob.TELENO AS 'Mobile Phone',
PL.DOCUMENT PPICTURE,
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, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
ELSE 'N/A'
END AS 'Contract Scan Status'
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 p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)
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
|