as

 
SELECT

 
p01.pin,org.name AS VesselName,

p01.FIRSTNAME AS FirstName,

 
p01.LASTNAME AS LastName,

p01.[NAME] AS NAME,

NULL AS OtherSeafarerID,

p01.ADDRESS1 AS Address1,

p01.ADDRESS2 AS Address2,

p01.ADDRESS3 AS Address3,

p01.POSTCODE AS PostalCode,

CONVERT(VARCHAR,p01.birthdate,23) AS BirthDate,

p01.POSTPLACE AS City,

p01.NATIONALITY AS Citizenship,

p01.ADDRESS_COUNTRY AS CountryCode,

us.COUNTYNAME AS StateRegion,

c02.NAME AS Occupation,

'Passport' AS Passport,

p08.TDNUMBER AS IDValue,

CONVERT(VARCHAR,p08.DATETO,23) AS IDExpire,

scan.DOCNO AS IDImage,

phone.TELENO AS MobilePhoneNumber,

email.TELENO AS EmailAddress,

'

COMPANY_NAME' AS AgencyName,

p01.CURRENCYFORPAYMENT AS ContractId,

CONVERT(VARCHAR,dbo.ad_signondate(p03.numorgid,p03.pin,p03.datefrom),23) AS SignOn,

CONVERT(VARCHAR,p03.dateto,23) AS ConfirmedSignOff,

CONVERT(VARCHAR,p03.todateestimated,23) AS plannedSignOff

FROM pw001p01 p01

LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY p03.PIN ORDER BY p03.DATEFROM DESC) AS Nr FROM pw001p03 p03

 
WHERE EXISTS(SELECT 1 FROM pw001c12 c12 WHERE c12.CODE=p03.CODE AND c12.OPTIONS LIKE '%S%') AND ISNULL(p03.PLANNED,'N') <> 'Y') p03

 
WHERE p03.Nr = 1) p03 ON p03.PIN=p01.PIN

LEFT JOIN (SELECT NUMORGID,dbo.ad_scanorgtree(NUMORGID, 3) AS VesselID FROM pworg WHERE ORGTYPE IN ('3', '4', '5')) ou ON ou.NUMORGID=p03.NUMORGID

LEFT JOIN pw001c12 c12 ON c12.CODE=p03.CODE

LEFT JOIN pworg org ON org.NUMORGID=ou.VesselID

LEFT JOIN pw001p0p p0p ON p01.PIN=p0p.PIN AND p0p.PNUMBER='A'

LEFT JOIN pw001c02 c02 ON c02.CODE=p0p.POSITIONID

LEFT JOIN PW001P08 AS p08 ON p08.PIN=p01.PIN AND EXISTS(SELECT 1 FROM PW001C23 C23 WHERE C23.CODE=P08.CODE AND C23.OPTIONS LIKE '%T%')

 
AND NOT EXISTS (SELECT 1 FROM pw001p08 p08t JOIN PW001C23 C23t on c23t.code=p08t.code and c23t.options like '%T%' WHERE p01.PIN=p08t.PIN AND p08t.DATETO > p08.DATETO)

LEFT JOIN PW001OLEDOCS scan ON p08.scanneddocno=scan.DOCNO

 
LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY P0T.PIN ORDER BY P0T.REPL_MODIFIEDDATE DESC) AS Nr

 
FROM PW001P0T p0t WHERE P0T.TELETYPE IN (3)) p0t WHERE p0t.Nr=1) phone ON phone.PIN=p01.PIN

LEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY P0T.PIN ORDER BY P0T.REPL_MODIFIEDDATE DESC) AS Nr

 
FROM PW001P0T p0t WHERE P0T.TELETYPE IN (6)) p0t WHERE p0t.Nr=1) email ON email.PIN=p01.PIN

LEFT JOIN pwpost post ON post.POSTCODE=p01.POSTCODE AND post.COUNTRYCODE=p01.ADDRESS_COUNTRY AND post.[NAME]=p01.POSTPLACE

LEFT JOIN PWCOUNTY us ON us.COUNTYCODE=post.COUNTYCODE AND us.COUNTRYCODE=post.COUNTRYCODE WHERE P03.DATEFROM IS NOT NULL

 
AND scan.docno is not null and p03.todateestimated is not null

GO