SELECT  p01.PIN,
		CASE 
            WHEN P01.SEX='M' THEN 'Mr. '+P01.LASTNAME
            WHEN P01.SEX='F' THEN 'Mrs. '+P01.LASTNAME
            ELSE 'Mr/Mrs. '+P01.LASTNAME
		END                  AS #LASTNAME,
		ves.[NAME]           AS #VESSELNAME,
		VES.NUMORGID,
		p03.DATEFROM         AS #DATEFROM,
		p03.DATETO           AS #DATETO,
		p03.TODATEESTIMATED  AS #EED,
		p03.SEQUENCENO,
		pf.[NAME]            AS #PLATFORM,
		email.TELENO           AS #EMAIL
FROM   PW001P01 P01
JOIN PW001P03 P03 ON P03.PIN = P01.PIN
					AND ISNULL(P03.PLANNED ,'N') = 'Y'
					--AND P03.DATEFROM = DATEADD(DAY ,DATEDIFF(DAY ,0 ,GETDATE()) ,30)
					AND P03.CODE IN (SELECT CODE FROM PW001C12 WHERE  OPTIONS LIKE '%S%')
LEFT JOIN (SELECT NUMORGID,dbo.ad_scanorgtree(NUMORGID ,3) AS VesselID FROM pworg) ou ON  ou.NUMORGID = p03.NUMORGID
LEFT JOIN pworg ves ON  ves.NUMORGID = ou.VesselID
LEFT JOIN PWCTCCMP pf ON  pf.COMPANYID = ves.NUMORGID
LEFT JOIN PW001P0T email ON email.PIN = p01.PIN 
							AND email.TELETYPE = 6 
							AND email.TELEPRIORITY IS NOT NULL 
							AND NOT EXISTS (SELECT 1  FROM   PW001P0T t WHERE  t.PIN = email.PIN AND t.TELETYPE = email.TELETYPE AND t.TELEPRIORITY IS NOT NULL
							AND (t.TELEPRIORITY < email.TELEPRIORITY OR t.TELEPRIORITY = email.TELEPRIORITY AND t.SEQUENCENO < email.SEQUENCENO))

WHERE EXISTS (
           SELECT PRT.SEQNO
           FROM   PWORGVESACT PRT
                  JOIN PWCCMCHANGECREWLIST CCL
                       ON  CCL.SEQNO = PRT.SEQNO
                           AND CCL.SIGNON = 1
                           AND CCL.CHANGEDONE = 0
                           AND Convert(date,CCL.repl_ModifiedDate) = Convert(date,GetDate())
                  JOIN PW001P01 P
                       ON  P.PIN = CCL.PIN
           WHERE  P.PIN = P01.PIN
       )