/
UniSea CrewData_API DB view
UniSea CrewData_API DB view
- Helen Ustych
Owned by Helen Ustych, created
1 Description | 2 Selection | 3 SQL statement
Summary | The view is used to provide Crew data to UniSea via API calls |
|---|---|
Keywords | Crew Data |
Category | Database View |
Description
The view is used to provide Crew data to UniSea via API calls
Selection
The view shows all the crew existing in the system.
SQL statement
Create View:
CREATE VIEW dbo.CrewData_API AS
SELECT P01.PIN,
P01.FIRSTNAME,
P01.LASTNAME,
P01.SEX GENDER,
C02.NAME RANK,
P01.NATIONALITY,
P01.BIRTHDATE,
P01.PLACEOFBIRTH,
P1P.PASSPORTNO PASSPORTNO,
P1P.PASSPORTEXPIRYDATE PASSPORTEXPIRY,
P1P.PASSPORTISSUEDATE,
P1P.PASSPORTISSUECOUNTRY,
P0T.TELENO EMAIL,
MOBILE.TELENO MOBILE,
CASE
WHEN p03.PLANNED <> 'Y' AND p03.DATETO IS NULL THEN 'Y'
ELSE 'N'
END AS ONBOARD_STATUS,
p03.SEQUENCENO ONB_ACTIVITY_SEQUENCENO
FROM PW001P01 P01
LEFT JOIN PW001P03 p03Cur
ON P01.PIN = p03Cur.PIN
AND p03Cur.DATETO IS NULL
AND ISNULL(p03Cur.PLANNED, 'N') <> 'Y'
AND EXISTS(
SELECT 1
FROM PW001C12 t1
WHERE t1.CODE = p03Cur.CODE
AND t1.OPTIONS LIKE '%S%'
)
LEFT JOIN (
SELECT *
FROM (
SELECT pl.PIN,
pl.SEQUENCENO,
pl.CODE,
ROW_NUMBER() OVER(PARTITION BY pl.PIN ORDER BY pl.DATEFROM ASC)Nr
FROM pw001p03 pl
WHERE pl.DATETO IS NULL
AND ISNULL(pl.PLANNED, 'N') = 'Y'
AND EXISTS(
SELECT 1
FROM PW001C12 t2
WHERE t2.CODE = pl.CODE
AND t2.OPTIONS LIKE '%S%'
)
)tpl
WHERE tpl.Nr = 1
)p03Planned
ON p01.pin = p03Planned.PIN
LEFT JOIN (
SELECT *
FROM (
SELECT lh.PIN,
lh.SEQUENCENO,
lh.CODE,
ROW_NUMBER() OVER(PARTITION BY lh.PIN ORDER BY lh.DATEFROM DESC)Nr
FROM pw001p03 lh
WHERE lh.DATETO IS NOT NULL
AND ISNULL(lh.PLANNED, 'N') <> 'Y'
AND EXISTS(
SELECT 1
FROM PW001C12 t3
WHERE t3.CODE = lh.CODE
AND t3.OPTIONS LIKE '%S%'
)
)tlh
WHERE tlh.Nr = 1
)p03LatestHistorical
ON p01.pin = p03LatestHistorical.PIN
JOIN PW001P03 P03
ON p03.SEQUENCENO = ISNULL(ISNULL(ISNULL(p03Cur.SEQUENCENO, p03Planned.SEQUENCENO), p03LatestHistorical.SEQUENCENO),p03Cur.SEQUENCENO)
LEFT JOIN PW001C02 C02
ON C02.CODE = P01.RANK
LEFT JOIN PW001P1P P1P
ON P01.PIN = P1P.PIN
AND p1p.code = 'PP'
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 MOBILE
ON (
(P01.PIN = MOBILE.PIN)
AND (MOBILE.TELETYPE = 3)
AND (NOT MOBILE.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001P0T MOBILE2
WHERE (MOBILE.PIN = MOBILE2.PIN)
AND (MOBILE2.TELETYPE = 3)
AND (
(MOBILE2.TELEPRIORITY < MOBILE.TELEPRIORITY)
OR (
(MOBILE2.TELEPRIORITY = MOBILE.TELEPRIORITY)
AND (MOBILE2.SEQUENCENO < MOBILE.SEQUENCENO)
)
)
)
)
--WHERE P01.EMPLOYMENTENDDATE IS NULL OR P01.EMPLOYMENTENDDATE > GETDATE() --when enabled - only for active crew
GODrop View:
DROP VIEW dbo.CrewData_API
GO