,
		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
       )