Onboard Crew
Summary | This view only displays persons currently onboard and 2 specific travel documents; Passport, Seamans Book, and C1D visa. |
|---|---|
Keywords | Sailing, Onboarding |
Description
This view only displays persons currently onboard and 2 specific travel documents; Passport, Seamans Book, and C1D visa.
View Sample
SQL Statement
It is necessary to change the codes for Passport, Visa, and Seaman’s Book according to the use codes from your system. Passport - P1P.CODE='P' Visa - C1D.CODE='VISAC1D' Seaman’s Book - SBOOK.CODE='SFR'
CREATE VIEW dbo.PW001SRV43
as
SELECT
p01.pin as Pin,
p01.Name as 'Name',
p01.Firstname 'First Name',
p01.MIDDLENAME 'Middle Name',
p01.Lastname as 'Last Name',
p01.Nationality,
c04.[TEXT] AS 'Marital Status',
COUNTRY.COUNTRYNAME AS 'Nationality Name',
p01.Sex as 'Gender',
case when p01.sex='F' then 'Female'
when p01.sex='M' then 'Male' end as 'Gender Name',
p01.Birthdate as 'DOB',
p01.PLACEOFBIRTH as 'Place of Birth',
vsl.name as Ship,
vsl.NUMORGID as Vessel_numorgid,
DEP.NAME as Department,
POS.NAME as Position,
--P03.CabinID as 'Cabin',
dbo.ad_SignOnDate(p03.NUMORGID, p01.PIN, P03.DATEFROM) as 'Sign On Date',
ISNULL(p03.DATETO, P03.TODATEESTIMATED) as 'Sign Off Date',
P1P.TDNUMBER as 'Passport Number',
P1P.DATEFROM as 'Passport Issue Date',
P1P.DATETO as 'Passport Expiry Date',
--C1D.TDNUMBER as 'C1D Number',
--C1D.DATEFROM as 'C1D Issue Date',
--C1D.DATETO as 'C1D Expiry Date',
SBOOK.TDNUMBER as 'SB Number',
SBOOK.DATEFROM as 'SB Issue Date',
SBOOK.DATETO as 'SB Expiry Date',
p01.Client numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate
FROM dbo.pw001p01 p01
LEFT JOIN PWCOUNTRY COUNTRY ON P01.NATIONALITY=COUNTRY.COUNTRYCODE
JOIN dbo.PW001P03 P03 ON P01.PIN = P03.PIN
AND P03.CODE in (SELECT c12.CODE
FROM PW001C12 c12
WHERE c12.OPTIONS LIKE '%S%') AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE())
AND P03.DATEFROM <= GETDATE()
AND P03.PLANNED <> 'Y'
JOIN PWORG VSL ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3)
LEFT JOIN dbo.PWORG DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4)
LEFT JOIN dbo.PWORG POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5)
LEFT JOIN PW001P08 P1P ON P01.PIN=P1P.PIN AND P1P.CODE='PP'
--LEFT JOIN PW001P08 C1D ON C1D.PIN=P01.PIN AND C1D.CODE='VISAC1D'
LEFT JOIN PW001P08 SBOOK ON SBOOK.PIN=P01.PIN AND SBOOK.CODE='SB'
LEFT JOIN PW001C04 c04 ON c04.CODE = p01.MARITALSTATUSFields/ Columns Specification
Column | Description/ Location in APM |
|---|---|
PIN | Personal Details > Personal > PIN |
Alternative PIN | Personal Details > Personal > Alternative PIN |
Name | Personal Details > Personal > Full Name |
Title Name | Personal Details > Personal > Title Name |
First Name | Personal Details > Personal > First Name |
Middle Name | Personal Details > Personal > Middle Name |
Last Name | Personal Details > Personal > Last Name |
Maiden Name | Personal Details > Personal > Maiden Name |
Gender | Personal Details > Personal > Gender |
Organization | Personal Details > Employment > Organization’s Name |
Current Rank | Personal Details > Employment > Current Rank |
Numorgid | Personal Details > Employment > Organization’s No |
EmploymentStartDate | Personal Details > Employment > Employment Start Date |
EmploymentEndDate | Personal Details > Employment > Employment End Date |
Termination Reason | Personal Details > Employment > Termination Reason |
Nationality | Personal Details > Personal > Nationality |
DOB | Personal Details > Personal > Birth date |
Personal ID | Personal Details > Personal > Personal ID |
Passport Number | Travel Documents > Current Passport No |
Passport Issue Date | Travel Documents > Current Passport Issue Date |
Passport Expiry | Travel Documents > Current Passport Date |
Passport Country of Issue | Travel Documents > Current Passport Issue Country |
SB Number | Travel Documents > Current Seamans Book Number |
SB Issue Date | Travel Documents > Current Seamans Book Issue Date |
SB Expiry Date | Travel Documents > Current Seamans Book Expiry Date |
Marital Status | Personal Details > Personal > Marital Status |