SELECT     
	p01.PIN, 
	p01.NAME, 
	p01.LASTNAME, 
	p01.FIRSTNAME, 
	p01.MIDDLENAME, 
	p01.PERSONALIDNO, 
	p01.NATIONALITY, 
	p01.SEX AS GENDER, 
	p01.BIRTHDATE,
	DATEDIFF(YEAR, p01.BIRTHDATE, GETDATE()) - CASE WHEN 100 * MONTH(GETDATE()) + DAY(GETDATE()) < 100 * MONTH(p01.birthdate) + DAY(p01.birthdate)	THEN 1 
		ELSE 0 
		END AS AGE, 
	ORG.NAME AS ORGANIZATION, 
	p01.ADDRESS1, 
	p01.ADDRESS2, 
	p01.ADDRESS3, 
	telephone.TELENO AS 'HOME PHONE',
	mobile.TELENO AS 'MOBILE', 
	email.TELENO AS EMAIL, 
	residencecountry.COUNTRYNAME AS 'ADDRESS COUNTRY', 
	c32.TEXT AS 'CONTRACT TYPE',
	p01.CONTRACTSTARTDATE AS 'CONTRACT START',
	p01.CONTRACTEXPIRYDATE AS 'CONTRACT END', 
	P03.DATEFROM AS 'ACTIVITY START', 
	ISNULL(P03.DATETO,P03.TODATEESTIMATED) AS 'ACTIVITY END', 
	DEP.NAME AS DEPARTMENT, 
	POS.NAME AS POSITION, pass.PASSPORTNO,
	pass.PASSPORTEXPIRYDATE AS 'PASS EXPIRY', 
	P03.RANK, VSL.NAME AS VESSEL, 
	DEP.NUMORGID AS department_numorgid,
	VSL.NUMORGID AS VESSEL_NUMORGID, 
	p01.CLIENT AS numorgid, 
	p01.EMPLOYMENTSTARTDATE, 
	p01.EMPLOYMENTENDDATE
	
FROM PW001P01 AS p01 
	
LEFT OUTER JOIN PWORG AS ORG ON p01.CLIENT = ORG.NUMORGID 
LEFT OUTER JOIN PW001C32 AS c32 ON c32.CODE = p01.CONTRACTTYPE 
INNER JOIN PW001P03 AS P03 ON p01.PIN = P03.PIN AND (P03.DATETO IS NULL OR P03.DATETO >= GETDATE()) AND P03.DATEFROM <= GETDATE() AND P03.PLANNED <> 'Y' 
INNER JOIN PW001C12 AS c12 ON c12.CODE = P03.CODE AND c12.OPTIONS LIKE '%S%' 
LEFT OUTER JOIN PWROT_SHIFT_ACTIVITIES AS psa ON psa.ACTIVITIES = P03.SEQUENCENO 
LEFT OUTER JOIN PWROT_ACTIVE_SHIFT AS pas ON pas.SEQUENCENO = psa.SEQUENCENO 
INNER JOIN PWORG AS VSL ON VSL.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 3) 
LEFT OUTER JOIN PWORG AS DEP ON DEP.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 4) 
LEFT OUTER JOIN PWORG AS POS ON POS.NUMORGID = dbo.ad_scanorgtree(P03.NUMORGID, 5) 
LEFT OUTER JOIN PWORGVESCAB AS PCAB ON PCAB.CABINID = P03.cabinid 
LEFT OUTER JOIN PWCCMCHANGECREWLIST AS cclj ON cclj.NEXTACT = P03.SEQUENCENO 
LEFT OUTER JOIN PWORGVESACT AS vaj ON vaj.SEQNO = cclj.SEQNO 
LEFT OUTER JOIN PWPORT AS portj ON portj.PORTCODE = vaj.PLACEFROM 
LEFT OUTER JOIN PWCCMCHANGECREWLIST AS ccll ON ccll.CURRENTACT = P03.SEQUENCENO 
LEFT OUTER JOIN PWORGVESACT AS val ON val.SEQNO = ccll.SEQNO 
LEFT OUTER JOIN PWPORT AS portl ON portl.PORTCODE = val.PLACEFROM 
LEFT OUTER JOIN PW001P0T AS mobile ON mobile.PIN = p01.PIN AND 
									  mobile.TELETYPE = 3 AND NOT EXISTS 
									  (SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = mobile.PIN) AND 
									  (TELETYPE = mobile.TELETYPE) AND 
									  (TELEPRIORITY < mobile.TELEPRIORITY)) OR 
									  ((PIN = mobile.PIN) AND 
									  (TELETYPE = mobile.TELETYPE) AND 
									  (TELEPRIORITY = mobile.TELEPRIORITY)) AND 
									  (SEQUENCENO > mobile.SEQUENCENO))
LEFT OUTER JOIN PW001P0T AS telephone ON telephone.PIN = p01.PIN AND 
										 telephone.TELETYPE = 2 AND NOT EXISTS 
										 (SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = telephone.PIN) AND 
										 (TELETYPE = telephone.TELETYPE) AND 
										 (TELEPRIORITY < telephone.TELEPRIORITY)) OR 
										 ((PIN = telephone.PIN) AND 
										 (TELETYPE = telephone.TELETYPE) AND 
										 (TELEPRIORITY = telephone.TELEPRIORITY)) AND 
										 (SEQUENCENO > telephone.SEQUENCENO)) 
LEFT OUTER JOIN PW001P0T AS email ON email.PIN = p01.PIN AND 
									 email.TELETYPE = 6 AND NOT EXISTS 
									 (SELECT 1 AS Expr1 FROM PW001P0T AS t WHERE ((PIN = email.PIN) AND 
									 (TELETYPE = email.TELETYPE) AND 
									 (TELEPRIORITY < email.TELEPRIORITY)) OR 
									 ((PIN = email.PIN) AND 
									 (TELETYPE = email.TELETYPE) AND 
									 (TELEPRIORITY = email.TELEPRIORITY)) AND 
									 (SEQUENCENO > email.SEQUENCENO))
LEFT OUTER JOIN PWCOUNTRY AS residencecountry ON residencecountry.COUNTRYCODE = p01.ADDRESS_COUNTRY 
LEFT OUTER JOIN PW001P1P AS pass ON pass.PIN = p01.PIN AND pass.CODE = 'PP' 
LEFT OUTER JOIN PW001C02 AS C02 ON C02.CODE = dbo.ad_orgPosC02Code(POS.ORGCODE)

WHERE (p01.PIN IN (SELECT PIN FROM PW001P0Y WHERE (IncomebasedPensionScheme = 'Y'))) AND 
	  (p01.BIRTHDATE >= CONVERT(DATETIME, '1970-01-01 00:00:00', 102))