CREATE VIEW dbo.PW001SRV18 AS
SELECT P01.PIN PIN, 
P01.NAME NAME,
P01.FIRSTNAME FIRSTNAME,
P01.LASTNAME LASTNAME,
P01.NATIONALITY NATIONALITY, 
C02.NAME RANK,

	ISNULL(ISNULL(CA.DATEFROM,HA.DATEFROM),PA.DATEFROM) as 'ACTIVITY START',
	ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED) as 'ACTIVITY END',

	CASE
		WHEN CA.SEQUENCENO IS NOT NULL THEN 'C'
		WHEN HA.SEQUENCENO IS NOT NULL THEN 'H'
		WHEN PA.SEQUENCENO IS NOT NULL THEN 'P'
		ELSE 'N'
	END as 'ACTIVITY',

	POS.[NAME] POSITION,
	DEP.[NAME] DEPARTMENT,
	VES.[NAME] VESSEL,

	CASE
		WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
		ELSE dbo.ad_AccruedLeave(P01.PIN,CONVERT(DATE,GETDATE()),'Y')
	END as 'LEAVE BALANCE TODAY',
	CASE
		WHEN ISNULL(ISNULL(CA.SEQUENCENO,HA.SEQUENCENO),PA.SEQUENCENO) IS NULL THEN 0.00
		ELSE dbo.ad_AccruedLeave(P01.PIN,ISNULL(ISNULL(CA.TODATEESTIMATED,HA.DATETO),PA.TODATEESTIMATED),'Y')
	END as 'LEAVE BALANCE SIGN-OFF',

P01.CLIENT NUMORGID, 
ORG.NAME ORGNAME, 
P01.BIRTHDATE BIRTHDATE,
P01.PERSONALIDNO, 
P01.TELEPHONE PHONE,
P01.EMPLOYMENTSTARTDATE, 
P01.EMPLOYMENTENDDATE, 
PC.COUNTRYNAME AS ADDRESSCOUNTRY,
P0T.TELENO EMAIL
FROM
PW001P01 P01
LEFT JOIN PW001P0P P0P ON ((P01.PIN=P0P.PIN) AND (P0P.PNUMBER='A'))
LEFT JOIN PW001C02 C02 ON P0P.POSITIONID=C02.CODE
LEFT JOIN PWORG ORG ON P01.CLIENT=ORG.NUMORGID
LEFT JOIN PW001C32 C32 ON P01.CONTRACTTYPE=C32.CODE
LEFT JOIN PWCOUNTRY PC ON P01.ADDRESS_COUNTRY = PC.COUNTRYCODE
LEFT JOIN PW001P0T P0T ON ((P01.PIN = P0T.PIN) AND (P0T.TELETYPE = 6) AND (NOT P0T.TELEPRIORITY IS NULL) AND NOT EXISTS
(SELECT SEQUENCENO
FROM  PW001P0T P0T2
WHERE  (P0T.PIN = P0T2.PIN) AND (P0T2.TELETYPE = 6) AND ((P0T2.TELEPRIORITY < P0T.TELEPRIORITY) OR
((P0T2.TELEPRIORITY = P0T.TELEPRIORITY) AND (P0T2.SEQUENCENO < P0T.SEQUENCENO)))))
LEFT JOIN PW001P03 CA
		ON CA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
			AND CA.PIN = P01.PIN AND Isnull(CA.PLANNED,'N') != 'Y'
			AND CA.DATETO IS NULL
LEFT JOIN PW001P03 HA
		ON HA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
			AND HA.PIN = P01.PIN AND Isnull(HA.PLANNED,'N') != 'Y'
			AND CA.SEQUENCENO IS NULL
			AND HA.DATETO IS NOT NULL
			AND HA.DATETO = (SELECT MAX(t.DATETO) FROM PW001P03 t
							WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') != 'Y'
								AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%')
								AND t.DATETO IS NOT NULL)
LEFT JOIN PW001P03 PA
		ON PA.CODE in (SELECT t.CODE FROM PW001C12 t WHERE t.OPTIONS LIKE '%S%')
			AND PA.PIN = P01.PIN AND Isnull(PA.PLANNED,'N') = 'Y'
			AND CA.SEQUENCENO IS NULL
			AND HA.SEQUENCENO IS NULL
			AND PA.DATEFROM = (SELECT MIN(t.DATEFROM) FROM PW001P03 t
							WHERE t.PIN = P01.PIN AND Isnull(t.PLANNED,'N') = 'Y'
								AND t.CODE in (SELECT c.CODE FROM PW001C12 c WHERE c.OPTIONS LIKE '%S%'))
LEFT JOIN PWORG POS
		ON POS.ORGTYPE = 5
			AND POS.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
LEFT JOIN PWORG DEP
		ON DEP.ORGTYPE = 4
			AND (DEP.NUMORGID = ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID)
				OR DEP.NUMORGID = POS.NUMORGIDABOVE)
LEFT JOIN PWORG VES
		ON VES.NUMORGID = dbo.ad_scanorgtree(ISNULL(ISNULL(CA.NUMORGID,HA.NUMORGID),PA.NUMORGID),3)