The view provides a comprehensive overview of all crew members in the database, detailing their current leave balance. This includes information as of today's date and for the conclusion date of their most recent or upcoming sea-service activity, be it already completed or planned for the future.
Keywords
Activity, Leave balance
Category
Crew List View
Description
The view provides a comprehensive overview of all crew members in the database, detailing their current leave balance. This includes information as of today's date and for the conclusion date of their most recent or upcoming sea-service activity, be it already completed or planned for the future.
The view can be used as a find form in the Rotation Planning Module.
View Sample
Main Data Selection
All the crew members.
SQL statement
CREATE VIEW dbo.PW001SRV18 AS
SELECT P01.PIN PIN,
P01.NAME NAME,
P01.FIRSTNAME FIRSTNAME,
P01.LASTNAME LASTNAME,
P01.NATIONALITY NATIONALITY,
C02.NAME RANK,
ISNULL(ISNULL(CA.DATEFROM,HA.DATEFROM),PA.DATEFROM) as 'ACTIVITY START',
ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED) as 'ACTIVITY END',
CASE
WHEN CA.SEQUENCENO IS NOT NULL THEN 'C'
WHEN HA.SEQUENCENO IS NOT NULL THEN 'H'
WHEN PA.SEQUENCENO IS NOT NULL THEN 'P'
ELSE 'N'
END as 'ACTIVITY',
POS.[NAME] POSITION,
DEP.[NAME] DEPARTMENT,
VES.[NAME] VESSEL,
CASE
WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
ELSE dbo.ad_AccruedLeave(P01.PIN,CONVERT(DATE,GETDATE()),'Y')
END as 'LEAVE BALANCE TODAY',
CASE
WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
ELSE dbo.ad_AccruedLeave(P01.PIN,ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED),'Y')
END as 'LEAVE BALANCE SIGN-OFF',
P01.CLIENT NUMORGID,
ORG.NAME ORGNAME,
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO,
P01.TELEPHONE PHONE,
P01.EMPLOYMENTSTARTDATE,
P01.EMPLOYMENTENDDATE,
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P0T.TELENO EMAIL
FROM
PW001P01 P01
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
LEFT JOIN PW001C32 C32 ON P01.CONTRACTTYPE=C32.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
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 PW001P03 CA
ON CA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND CA.PIN = P01.PIN AND Isnull(CA.PLANNED,'N') != 'Y'
AND CA.DATETO IS NULL
LEFT JOIN PW001P03 HA
ON HA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND HA.PIN = P01.PIN AND Isnull(HA.PLANNED,'N') != 'Y'
AND CA.SEQUENCENO IS NULL
AND HA.DATETO IS NOT NULL
AND HA.DATETO = (SELECT MAX(t.DATETO) FROM PW001P03 t
WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') != 'Y'
AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%')
AND t.DATETO IS NOT NULL)
LEFT JOIN PW001P03 PA
ON PA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
AND PA.PIN = P01.PIN AND Isnull(PA.PLANNED,'N') = 'Y'
AND CA.SEQUENCENO IS NULL
AND HA.SEQUENCENO IS NULL
AND PA.DATEFROM = (SELECT MIN(t.DATEFROM) FROM PW001P03 t
WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') = 'Y'
AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%'))
LEFT JOIN PWORG POS
ON POS.ORGTYPE = 5
AND POS.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
LEFT JOIN PWORG DEP
ON DEP.ORGTYPE = 4
AND (DEP.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
OR DEP.NUMORGID = POS.NUMORGIDABOVE)
LEFT JOIN PWORG VES
ON VES.NUMORGID = dbo.ad_scanorgtree(ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID),3)
Columns Specification
Column
Description/ Location in APM
Column
Description/ Location in APM
PIN
PIN
NAME
Name
FIRSTNAME
First Name
LASTNAME
Last Name
NATIONALITY
Nationality code
RANK
Current Rank
ACTIVITY START
Start date of the target activity
ACTIVITY END
Estimated end date or date to of the target activity
ACTIVITY
Activity type: H - historical, C - current, P - planned, N - no activity
POSITION
Name of the position linked to the target activity
DEPARTMENT
Name of the department linked to the target activity
VESSEL
Name of the vessel linked to the target activity
LEAVE BALANCE TODAY
Leave balance as of today
LEAVE BALANCE SIGN-OFF
Leave balance for the end date of the target activity
ORGNAME
Employment organization name
BIRTHDATE
Birthdate
PERSONALIDNO
Personal ID number
PHONE
Mobile phone number
EMPLOYMENTSTARTDATE
Employment Start Date
EMPLOYMENTENDDATE
Employment End Date
ADDRESSCOUNTRY
Address Country
EMAIL
Email
Start Page
N/A
Other
The view can be used as a Find Form in the Rotation Planning Module.
