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,
    PPP01.NAME AS 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 PWPOST PP ON (P01.POSTCODE = PP.POSTCODE AND P01.ADDRESS_COUNTRY = PP.COUNTRYCODE)
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)))))