/
UniSea FamilyMembers_API DB view
UniSea FamilyMembers_API DB view
- Helen Ustych
Owned by Helen Ustych
1 Description | 2 Selection | 3 SQL statement
Summary | The view is used to provide information about the Family members to UniSea via API calls |
|---|---|
Keywords | Family, Next of Kin |
Category | Database View |
Description
The view is used to provide information about the Family members to UniSea via API calls
Selection
The view shows all the Family members existing in the system.
SQL statement
Create View:
CREATE VIEW dbo.FamilyMembers_API AS
SELECT P06.PIN,
P06.FIRSTNAME,
P06.LASTNAME,
C07.TEXT RELATION,
P0T.TELENO EMAIL,
MOBILE.TELENO MOBILE,
PHONE.TELENO PHONE,
CASE
WHEN p03.PLANNED <> 'Y' AND p03.DATETO IS NULL THEN 'Y'
ELSE 'N'
END AS ONBOARD_STATUS,
p03.SEQUENCENO ONB_ACTIVITY_SEQUENCENO
FROM PW001P06 P06
LEFT JOIN PW001C07 C07
ON C07.CODE = P06.RELATION
LEFT JOIN PW001PT6 P0T
ON (
(P06.SEQUENCENO = P0T.RELATIONSEQ)
AND (P0T.TELETYPE = 6)
AND (NOT P0T.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001PT6 P0T2
WHERE (P0T.RELATIONSEQ = P0T2.RELATIONSEQ)
AND (P0T2.TELETYPE = 6)
AND (
(P0T2.TELEPRIORITY < P0T.TELEPRIORITY)
OR (
(P0T2.TELEPRIORITY = P0T.TELEPRIORITY)
AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)
)
)
)
)
LEFT JOIN PW001PT6 MOBILE
ON (
(P06.SEQUENCENO = MOBILE.RELATIONSEQ)
AND (MOBILE.TELETYPE = 3)
AND (NOT MOBILE.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001PT6 MOBILE2
WHERE (MOBILE.RELATIONSEQ = MOBILE2.RELATIONSEQ)
AND (MOBILE2.TELETYPE = 3)
AND (
(MOBILE2.TELEPRIORITY < MOBILE.TELEPRIORITY)
OR (
(MOBILE2.TELEPRIORITY = MOBILE.TELEPRIORITY)
AND (MOBILE2.SEQUENCENO < MOBILE.SEQUENCENO)
)
)
)
)
LEFT JOIN PW001PT6 PHONE
ON (
(P06.SEQUENCENO = PHONE.RELATIONSEQ)
AND (PHONE.TELETYPE = 2)
AND (NOT PHONE.TELEPRIORITY IS NULL)
AND NOT EXISTS
(
SELECT SEQUENCENO
FROM PW001PT6 PHONE2
WHERE (PHONE.RELATIONSEQ = PHONE2.RELATIONSEQ)
AND (PHONE2.TELETYPE = 2)
AND (
(PHONE2.TELEPRIORITY < PHONE.TELEPRIORITY)
OR (
(PHONE2.TELEPRIORITY = PHONE.TELEPRIORITY)
AND (PHONE2.SEQUENCENO < PHONE.SEQUENCENO)
)
)
)
)
LEFT JOIN PW001P01 P01
ON P01.PIN = P06.PIN
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)
--WHERE P01.EMPLOYMENTENDDATE IS NULL OR P01.EMPLOYMENTENDDATE > GETDATE() --when enabled - only for active crew
GODrop View:
DROP VIEW dbo.FamilyMembers_API
GO