Summary	This view displays persons who are planned to sign on, that is those that have Planned sea-service Activities
Keywords	Embarking, Sign-On
Category	Crew List View

Description

This view displays persons who are planned to sign on, that is those that have Planned sea-service Activities. It has a column Confirmed Sign On Date that shows a check mark if the shift start date was confirmed in Rotation Planning Module:

View Sample

Main Data Selection

The view shows all the crew that have planned sea-service activities. The crew mebers are shown as many times as the number of planned sea service activities they have.

SQL statement

CREATE VIEW dbo.PW001SRV10 AS
SELECT 
       p01.PIN,
       P01.client NUMORGID,
       plan_ves.numorgid VESSEL_NUMORGID,
       plan_dep.numorgid as department_numorgid,
       p01.EMPLOYMENTSTARTDATE,
       p01.EMPLOYMENTENDDATE,
       p01.NAME,
       p01.FIRSTNAME,
       p01.LASTNAME,
       P01.MAIDENNAME AS 'LASTNAME 2',
       p01.MIDDLENAME,
      ORG.NAME ORGANIZATION,
       P01.BIRTHDATE,
       p01.PLACEOFBIRTH,
       P01.SEX GENDER,
       P01.NATIONALITY AS NAT,
       COUNTRY.COUNTRYNAME  NATIONALITY,
ISNULL(C02.NAME, rank.name) RANK,
P0T.TELENO as EMAIL,
       P08.TDNUMBER PASSPORT,
       P08.DATEFROM 'PASSPORT ISSUE DATE',
       p08.issuedby 'PASSPORT ISSUED BY',
       P08.DATETO  'PASSPORT EXPIRY',
       p08.ISSUE_COUNTRY 'ISSUE COUNTRY',
      P081.TDNUMBER  SBOOK,
       P081.DATEFROM  'SB ISSUE DATE',
       p081.issuedby 'SB ISSUE BY',
       P081.DATETO  'SBOOK EXPIRY',
       p01.contractstartdate 'CONTRACT START',
       p01.Contractexpirydate  'CONTRACT END',
       c12cur.TEXT  'CUR ACTIVITY',
       p03cur.DATEFROM  'CUR ACTIVTY START',
       --DATEDIFF(DAY, p03cur.DATEFROM, GETDATE()) + 1  DAYS,
       p03cur.TODATEESTIMATED  'CUR ACTIVTY END',
       p03plan.DATEFROM  'ACT PLANNED START',
CASE
       WHEN pas.CONFIRMED_SIGNON = 'Y' THEN NCHAR(10004) -- check mark

END AS 'Confirmed SignOn Date',
       p03plan.TODATEESTIMATED 'ACT PLANNED END',
      p03plan.CODE 'PLANNED ACTIVITY',
       plan_ves.NAME  VESSEL,
       PLAN_DEP.NAME  DEPARTMENT,
       --c02.groupno  'Sort',
       plan_pos.NAME  POSITION,
       pas.COMMENTS  COMMENT,
       P01.homeairport  HOMEAIRPORT,
p01.ADDRESS1,
    p01.ADDRESS2,
    p01.ADDRESS3,
    p01.POSTCODE,
    P01.POSTPLACE,
    PC.COUNTRYNAME AS ADDRESSCOUNTRY,
       ISNULL(portOn.NAME, portEst.NAME)  'ONSIGNING PORT',
	   ISNULL(portOff.NAME, portEstOff.NAME)  'OFFSIGNING PORT'
    
FROM   PW001P01 p01
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
JOIN PW001P03 p03plan ON  p03plan.PIN = p01.PIN AND p03plan.PLANNED = 'Y' AND NOT EXISTS (SELECT 1 FROM PW001P03 t
                                 JOIN PW001C12 c12t ON c12t.CODE = t.CODE AND c12t.OPTIONS LIKE '%S%'
                            WHERE t.PIN = p03plan.Pin AND dbo.ad_scanorgtree(t.NUMORGID, 3) = dbo.ad_scanorgtree(p03plan.NUMORGID, 3) AND t.Numorgid <> p03plan.Numorgid
                                  AND t.TODATEESTIMATED = p03plan.DATEFROM - 1)
JOIN PW001C12 c12plan ON  c12plan.CODE = p03plan.CODE AND c12plan.OPTIONS LIKE '%S%'
LEFT JOIN PWROT_SHIFT_ACTIVITIES psa ON psa.ACTIVITIES = p03plan.SEQUENCENO
LEFT JOIN PWROT_ACTIVE_SHIFT pas ON pas.SEQUENCENO = psa.SEQUENCENO
LEFT JOIN PWORG plan_ves ON  plan_ves.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
LEFT JOIN PWORG PLAN_DEP ON PLAN_DEP.NUMORGID = dbo.ad_scanorgtree(P03PLAN.NUMORGID, 4)
LEFT JOIN PWORG plan_pos ON  plan_pos.NUMORGID = p03plan.NUMORGID AND plan_pos.ORGTYPE = 5
LEFT JOIN PW001P03 p03cur ON  p03cur.PIN = p01.PIN AND p03cur.PLANNED <> 'Y'AND p03cur.DATEFROM <= GETDATE() AND ISNULL(p03cur.DATETO, GETDATE()) >= GETDATE()
LEFT JOIN PW001C12 c12cur ON  c12cur.CODE = p03cur.CODE    
LEFT JOIN PW001C02 C02 ON C02.code = dbo.ad_orgPosC02Code(PLAN_POS.ORGCODE)
LEFT JOIN PW001C02 rank ON rank.code = p01.rank
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PWCOUNTRY COUNTRY ON P01.NATIONALITY=COUNTRY.COUNTRYCODE
LEFT JOIN PW001P08 P08 ON P08.PIN=P01.PIN AND P08.CODE='PPORT'
LEFT JOIN PW001P08 P081 ON P081.PIN=P01.PIN AND P081.CODE='SBOTH'
LEFT JOIN PWCCMCHANGECREWLIST cclOn ON cclOn.NEXTACT = p03plan.SEQUENCENO
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 = p03plan.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
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)))))

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	PIN
VESSEL_NUMORGID	VESSEL_NUMORGID
NAME	NAME
LASTNAME	LASTNAME
RANK	Rank name from Personal Details
POSITION	Position name from Activity
DEPARTMENT	Department name from Activity
NAT	Nationality code
ACT PLANNED START	Start date of the Planned sea-service activity
Confirmed SignOn Date	Shows a tick mark if the start date of the Rotation shift that corresponds to the target activity is confirmed.
EMPLOYMENT STARTDATE	EMPLOYMENT STARTDATE
EMPLOYMENT ENDDATE	EMPLOYMENT ENDDATE
department_numorgid	department_numorgid
FIRSTNAME	FIRSTNAME
LASTNAME 2	Maiden Name where the 2nd Last name that is applicable for the Spanish is stored
MIDDLENAME	MIDDLENAME
ORGANIZATION	Employment company name (P01.CLIENT)
BIRTHDATE	BIRTHDATE
PLACEOFBIRTH	PLACEOFBIRTH
GENDER	GENDER
NAT	Nationality name
EMAIL	EMAIL
PASSPORT	Passport number
PASSPORT ISSUE DATE	PASSPORT ISSUE DATE
PASSPORT ISSUED BY	PASSPORT ISSUED BY
PASSPORT EXPIRY	PASSPORT EXPIRY
ISSUE COUNTRY	ISSUE COUNTRY
SBOOK	Seaman’s book number
SB ISSUE DATE	SB ISSUE DATE
SB ISSUE BY	SB ISSUE BY
SBOOK EXPIRY	SBOOK EXPIRY
CONTRACT START	CONTRACT START DATE from Personal Details
CONTRACT END	CONTRACT END DATE from Personal Details
ACT PLANNED END	Estimated End Date of the Planned sea-service activity
CUR ACTIVITY	Current activity name
CUR ACTIVTY START	Current activity start date
CUR ACTIVTY END	Current activity estimated end date
PLANNED ACTIVITY	Planned activity name
COMMENT	Comment from the Rotation shift corresponding to the Planned sea-service activity
VESSEL	Vessel name of the target activity
HOMEAIRPORT	HOMEAIRPORT
ADDRESS1	ADDRESS1
ADDRESS2	ADDRESS2
ADDRESS3	ADDRESS3
POSTCODE	POSTCODE
POSTPLACE	POSTPLACE
ADDRESSCOUNTRY	ADDRESSCOUNTRY
ONSIGNING PORT	ONSIGNING PORT
OFFSIGNING PORT	OFFSIGNING PORT

Start Page

N/A

Other

The field ACT PLANNED START is set up to be highlighted with Yellow and Red depending on the number of days from today:

Reports, Views and Similar

Embarking Crew