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