CREATE VIEW dbo.PW001SRV12
AS
SELECT
P01.PIN 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',
-- P01.CALLINGNAME AS 'Calling Name',
-- P01.SUFFIXNAME AS 'Suffix Name',
ORG.NAME as 'Organization',
C02.NAME 'Current Rank',
-- C02.GROUPNO AS 'Rank Sort',
P01.CLIENT Numorgid,
P01.EmploymentStartDate ,
P01.EmploymentEndDate,
P01.NATIONALITY AS Nationality,
-- PV.NAME PAYROLLVESSEL,
P01.BIRTHDATE 'Birth date',
P01.PERSONALIDNO as 'Personal ID',
PASS.TDNUMBER 'Passport No',
PASS.DATETO as 'Passport Expiry',
-- P0P.STARTDATE P0PSTARTDATE,
-- P0P.ENDDATE P0PENDDATE,
-- P01.PERSONGROUPCODE 'Cost Group',
-- c33.Name AS 'Category A',
P01.ADDRESS1+' '+P01.ADDRESS2+' '+P01.ADDRESS3 as 'Address',
P01.POSTCODE 'Post Code',
P01.POSTPLACE 'Post Place',
PC.COUNTRYNAME AS 'Address Country',
P01.HOMEAIRPORT 'Home Airport',
-- P01.HOMEAIRPORT2,
email.TELENO AS 'E-MAIL',
P01.TELEPHONE 'Phone',
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'
FROM
PW001P01 P01
LEFT JOIN (SELECT P8.PIN, P8.tdnumber, P8.datefrom, P8.dateto, p8.ISSUEDWHERE, p8.nativename FROM dbo.PW001P08 P8
JOIN dbo.PW001C23 C23 ON P8.CODE=C23.CODE and C23.OPTIONS LIKE '%T%'
WHERE P8.PASSPORTPRIORITY=0) pass ON pass.PIN = P01.PIN
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
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 p01.PIN=p20.PIN AND p20.HISTORICAL='F' and p20.Datestart<=getdate() and (p20.dateend>=getdate() or p20.DATEEND is NULL) and CONTRACTKIND=0
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
)
)
