Summary	The view shows all the crew in the database and leave balance as of today and for the end date of the current or last history or next planned sea-service activity.
Keywords	Activity, Leave balance
Category	Crew List View

Description

The view shows all the crew in the database and leave balance as of today and for the end date of the current or last history or next planned sea-service activity. The view is used as a find form in Rotation Planning Module.

View Sample

Main Data Selection

The view shows all the crew in the database and leave balance as of today and for the sign off date of the current or last history or next planned sea-service activity.

SQL statement

Click here to expand...

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
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 is used as a Find form in Rotation Planning Module.

Leave Balance