CREATE VIEW [dbo].[API_Shipmoney] 
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