Onboard Crew
- Sigrid Kviteberg
- Roman Zelenskyi
- Daria Chebotaieva
- Arvin John Salandanan
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.PW001SRV53
AS
SELECT p01.pin AS Pin,
--P01.ALTERNATIVEPIN AS 'ALTERNATIVE PIN',
p01.Name AS 'Name',
p01.Firstname 'First Name',
p01.MIDDLENAME 'Middle Name',
p01.Lastname AS 'Last Name',
P1P.NATIVENAME 'Native Name',
p01.Nationality,
COUNTRY.COUNTRYNAME AS 'Nationality Name',
p01.Sex AS 'Gender',
C04.[TEXT] AS 'Marital Status',
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',
P01.PERSONALIDNO 'Personal ID',
vsl.name AS Vessel,
vsl.NUMORGID 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',
--IMPLADC-568 06/27/2024
/*
ISNULL(portOn.portcode, portEst.portcode) 'EMBARKATION PORT CODE',
ISNULL(portOn.NAME, portEst.NAME) 'EMBARKATION PORT',
ISNULL(portOff.portcode, portEstOff.portcode) 'DISEMBARKATION PORT CODE',
ISNULL(portOff.NAME, portEstOff.NAME) 'DISEMBARKATION PORT',
*/
portOn.portcode 'EMBARKATION PORT CODE',
portOn.NAME 'EMBARKATION PORT',
portOff.portcode 'DISEMBARKATION PORT CODE',
portOff.NAME 'DISEMBARKATION PORT',
P1P.TDNUMBER AS 'Passport Number',
P1P.DATEFROM AS 'Passport Issue Date',
P1P.DATETO AS 'Passport Expiry Date',
SBOOK.TDNUMBER AS 'SB Number',
SBOOK.DATEFROM AS 'SB Issue Date',
SBOOK.DATETO AS 'SB Expiry Date',
--C1D.TDNUMBER as 'C1D Number',
--C1D.DATEFROM as 'C1D Issue Date',
--C1D.DATETO as 'C1D Expiry Date',
p01.Client numorgid,
p01.EmploymentStartDate,
p01.EmploymentEnddate,
/* CASE
WHEN CONTRACTKIND = 0 THEN 'Main Contract'
WHEN CONTRACTKIND = 1 THEN 'Sub Contract'
WHEN CONTRACTKIND = 2 THEN 'Ammendment'
ELSE ''
END AS 'Contract Type',
C32CONT.Text 'Contract Name',
C02CONT.NAME 'Contract Rank',
p20.DATESTART 'Contract Start',
p20.DATEEND 'Contract End',
p20.DURATION 'Trial period duration',
p20.TRIALPERIODEND 'Trial period end',
p20.PAYSCALETABLE 'Contract Pasycale Table',
p20.PAYSCALECODE 'Contract Pasycale Code',
p0u.EMP_PORT 'Employee Portal Access From',
p20u.[LENGTH] + ' months' AS 'Duration Of Employment', */
p01.[RANK] AS 'Current Rank Code',
cRANK.[NAME] AS 'Current Rank Name',
cRANK.GROUPNO AS 'Rank sort',
C56.NAME AS 'Rank Category',
org.[NAME] AS 'Organization',
/* CASE
WHEN p20.scanneddocno IS NULL THEN NCHAR(9940) + ' Missing'
WHEN p20.scanvalidity IN (1, 0) AND p20.scanneddocno IS NOT NULL THEN NCHAR(10004) + ' Confirmed'
ELSE 'N/A'
END AS 'Contract Scan Status', */
emb.[TEXT] AS 'Embarking Reason',
dis.[TEXT] AS 'Disembarking Reason',
p03.SEQUENCENO AS 'External ID'
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'
LEFT JOIN PWROT_SHIFT_ACTIVITIES psa
ON psa.ACTIVITIES = p03.SEQUENCENO
LEFT JOIN PWROT_ACTIVE_SHIFT pas
ON pas.SEQUENCENO = psa.SEQUENCENO
LEFT JOIN PWCCMCHANGECREWLIST cclOn
ON cclOn.NEXTACT = p03.SEQUENCENO
AND cclOn.SIGNON = 1
LEFT JOIN PWORGVESACT ccOn
ON ccOn.SEQNO = cclOn.SEQNO
LEFT JOIN PWPORT portOn
ON portOn.PORTCODE = ccOn.PLACEFROM
LEFT JOIN PWORGVESACT ccEst
ON ccEst.SEQNO = pas.SPD_CC_SIGNON
LEFT JOIN PWPORT portEst
ON portEst.PORTCODE = ccEst.PLACEFROM
LEFT JOIN PWCCMCHANGECREWLIST cclOff
ON cclOff.CURRENTACT = p03.SEQUENCENO
LEFT JOIN PWORGVESACT ccOff
ON ccOff.SEQNO = cclOff.SEQNO
LEFT JOIN PWPORT portOff
ON portOff.PORTCODE = ccOff.PLACEFROM
LEFT JOIN PWORGVESACT ccEstOff
ON ccEstOff.SEQNO = pas.SPD_CC_SIGNON
LEFT JOIN PWPORT portEstOff
ON portEstOff.PORTCODE = ccEstOff.PLACEFROM
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 = 'SBOOK'
AND NOT EXISTS (
SELECT 1
FROM PW001P08 t
WHERE t.PIN = SBOOK.PIN
AND t.CODE = 'SBOOK'
AND t.DATETO > SBOOK.DATETO
)
LEFT JOIN PW001P20 p20
ON p20.PIN = p01.PIN
AND p20.HISTORICAL = 'F'
--AND p20.DATESTART BETWEEN p03plan.DATEFROM AND ISNULL(p03plan.DATETO, p03plan.TODATEESTIMATED)
AND NOT EXISTS (
SELECT 1
FROM PW001P20 t
WHERE t.PIN = p20.PIN
AND t.HISTORICAL = 'F'
AND t.DATESTART > p20.DATESTART
)
LEFT JOIN PW001OLEDOCS sdoc
ON sdoc.DOCNO = p20.SCANNEDDOCNO
LEFT JOIN PW001C02 C02CONT
ON p20.RANK = C02CONT.CODE
LEFT JOIN PW001C32 C32CONT
ON p20.CONTRACTTYPE = C32CONT.code
LEFT JOIN PW001P0U p0u
ON p0u.PIN = p01.PIN
LEFT JOIN PW001P20U p20u
ON p20u.CONTRACT_SEQNO = p20.SEQUENCENO
LEFT JOIN PW001C02 cRANK
ON cRank.CODE = p01.[RANK]
LEFT JOIN PWORG org
ON org.NUMORGID = p01.CLIENT
LEFT JOIN PW001C79 emb
ON emb.CODE = p03.Embarkation
LEFT JOIN PW001C79 dis
ON dis.CODE = p03.Disembarkation
LEFT JOIN PW001C43 c43
ON c43.CODE = p01.COSTPLACE
LEFT JOIN PW001C04 c04
ON c04.CODE = p01.MARITALSTATUS
/*LEFT JOIN PW001C02 c02
ON c02.CODE = p01.RANK*/
LEFT JOIN PW001C56 c56
ON c56.CODE = cRANK.CODECATEGORY
Fields/ 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 |
|
Rank Category | Rank Category |
|
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 |
|