CREATE VIEW dbo.PW001SRV134 AS
SELECT p01.pin AS PIN,
P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
CASE
WHEN p01.sex = 'M' THEN 'Mr'
WHEN p01.sex = 'F' THEN 'Mrs'
END AS Gender,
p01.NAME AS 'Full Name',
p01.FIRSTNAME AS 'First Name',
p01.MIDDLENAME AS 'Middle Name',
p01.LASTNAME AS 'Last Name',
DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate) 'Celebration Date',
MONTH(p01.birthdate) AS 'Month Nr',
DATENAME(
mm,
DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), p01.birthdate)
) MONTH,
CONVERT(
INT,
DATENAME(
d,
DATEADD(YY, DATEDIFF(YY, P01.BIRTHDATE, GETDATE()), BIRTHDATE)
)
) AS DATE,
DATENAME(
wk,
DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
) Week,
DATENAME(
weekday,
DATEADD(yy, DATEDIFF(yy, p01.birthdate, GETDATE()), birthdate)
) DAY,
DATEDIFF(yy, p01.birthdate, GETDATE()) Age,
p01.Birthdate,
p01.COUNTRYOFBIRTH + ' - ' + COUNTRY.COUNTRYNAME AS 'Country / Location',
PCAB.Cabin,
POS.NAME AS Position,
P0T.TELENO AS Email,
mob.Teleno AS 'Mobile Phone',
p01.RANK AS 'Current Rank',
p01.Client numorgid,
VSL.NUMORGID AS vessel_numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate,
p01.COSTPLACE AS 'Department/Cost Place Code',
c43.[NAME] AS 'Department/Cost Place'
FROM dbo.pw001p01 p01
JOIN dbo.PW001P03 P03
ON P01.PIN = P03.PIN
AND ISNULL(P03.PLANNED,'N') <> 'Y'
AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
LEFT JOIN Pw001C12 act
ON act.code = p03.code
JOIN PWORG VSL
ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
LEFT JOIN dbo.PWORG DEP
ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
LEFT JOIN dbo.PWORG POS
ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
LEFT JOIN dbo.PWORGVESCAB PCAB
ON PCAB.CABINID = p03.cabinid
LEFT JOIN PWCOUNTRY COUNTRY
ON P01.COUNTRYOFBIRTH = COUNTRY.COUNTRYCODE
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
LEFT JOIN PW001P0T P0T
ON (
(P01.PIN = P0T.PIN)
AND (P0T.TELETYPE = 6)
AND (NOT P0T.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001P0T P0T2
WHERE (P0T.PIN = P0T2.PIN)
AND (P0T2.TELETYPE = 6)
AND (
(P0T2.TELEPRIORITY < P0T.TELEPRIORITY)
OR (
(P0T2.TELEPRIORITY = P0T.TELEPRIORITY)
AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)
)
)
)
)
LEFT JOIN PW001P0T mob
ON (
(P01.PIN = mob.PIN)
AND (mob.TELETYPE = 3)
AND (NOT mob.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001P0T mob2
WHERE (mob.PIN = mob2.PIN)
AND (mob2.TELETYPE = 3)
AND (
(mob2.TELEPRIORITY < mob.TELEPRIORITY)
OR (
(mob2.TELEPRIORITY = mob.TELEPRIORITY)
AND (mob2.SEQUENCENO < mob.SEQUENCENO)
)
)
)
)
WHERE act.OPTIONS LIKE '%S%'
|