Versions Compared

Version Old Version 1 New Version Current
Changes made by

Helen Ustych

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Page Properties

Summary

The view shows provides a comprehensive overview of all the crew members in the database and , detailing their current leave balance. This includes information as of today's date and for the end conclusion date of the current or last history or next planned 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 shows provides a comprehensive overview of all the crew members in the database and , detailing their current leave balance. This includes information as of today's date and for the end conclusion date of the current or last history or next planned their most recent or upcoming sea-service activity, be it already completed or planned for the future.

The view is can be used as a find form in the 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 activityAll the crew members.

SQL statement

Expand
Code Block
languagesql
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)

...

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 can be used as a Find form Form in the Rotation Planning Module.

...