Summary	This view shows the crew that have no Onboard activity and is used as a find form in Rotation Planning Module to identify the newly employed crew that are ready for scheduling. Customized fields are used in the view.
Keywords	Scheduling, find form, Rotation Planning Module
Category	Crew List View

Description

This view shows the crew that have no Onboard activity and is used as a find form in Rotation Planning Module to identify the newly employed crew that are ready for scheduling.

Customized fields are used in the view.

View Sample

Main Data Selection

The view shows the Employed crew that have no sea-service (onboard) activity.

SQL statement

SQL Statement

--Available for scheduling
CREATE VIEW dbo.PW001SRV16 AS
SELECT P01.PIN PIN, 
P01.NAME NAME,
P01.FIRSTNAME FIRSTNAME,
P01.LASTNAME LASTNAME,
P01.NATIONALITY NATIONALITY, 
C02.NAME RANK,
P0U.AVAIL 'AVAILABLE FROM',
VES.NAME VESSEL,
P01.CLIENT NUMORGID, 
ORG.NAME ORGNAME, 
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO, 
P01.TELEPHONE PHONE,
P1P.PASSPORTEXPIRYDATE PASSPORTEXPIRY,
P1P.PASSPORTNO PASSPORTNO, 
P01.EMPLOYMENTSTARTDATE, 
P01.EMPLOYMENTENDDATE, 
C32.TEXT CONTRACTTYPE, 
P01.CONTRACTSTARTDATE, 
P01.CONTRACTEXPIRYDATE,
P01.ADDRESS1,
P01.ADDRESS2,
P01.POSTCODE,
P01.POSTPLACE,
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P01.HOMEAIRPORT, 
P01.HOMEAIRPORT2, 
P0T.TELENO EMAIL
FROM
PW001P01 P01
LEFT JOIN PW001P1P P1P ON P01.PIN=P1P.PIN
LEFT JOIN PW001P0U P0U ON P0U.PIN=P01.PIN
LEFT JOIN PWORG VES ON VES.NUMORGID=P0U.ORG
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 PW001C38 C38 ON P01.PAYROLLSTATUS=C38.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P01PICT PL ON P01.PIN = PL.PIN
LEFT JOIN PWORG PV ON P01.PAYROLLVSL=PV.NUMORGID
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)))))
WHERE P01.PIN not in (SELECT A.PIN FROM PW001P03 A WHERE A.CODE in (SELECT T.CODE FROM PW001C12 T WHERE T.OPTIONS like '%S%'))
sql select statement

Columns Specification

Column	Description/ Location in APM
PIN	PIN
NAME	NAME
FIRSTNAME	FIRSTNAME
LASTNAME	LASTNAME
NAT	Nationality
RANK	Rank Name from Personal Details > Current Rank
AVAILABLE FROM	Customized field Available From: (see screen shot below)
VESSEL	Customized field Vessel:
ORG. NAME	Employment company name
BIRTHDATE	BIRTHDATE
PERSONALIDNO	Personal ID Number
PHONE	Home Phone
PASSPORT EXPIRY	Primary passport expiry date
PASSPORTNO	Primary passport number
EMPLOYMENT STARTDATE	EMPLOYMENT STARTDATE
EMPLOYMENT ENDDATE	EMPLOYMENT ENDDATE
CONTRACT TYPE	CONTRACT TYPE
CONTRACT START DATE	CONTRACT START DATE
CONTRACT EXPIRY DATE	CONTRACT EXPIRY DATE
ADDRESS1	ADDRESS1
ADDRESS2	ADDRESS2
POSTCODE	POSTCODE
POSTPLACE	POSTPLACE
ADDRESSCOUNTRY	ADDRESS COUNTRY
HOMEAIRPORT	HOMEAIRPORT
HOMEAIRPORT2	HOMEAIRPORT2
EMAIL	EMAIL

Start Page

N/A

Other

Is used as a find form in Rotation Planning Module to identify the newly employed crew that are ready for scheduling.

Available for Scheduling (no Onboard Activity)