CREATE VIEW dbo.VIEWID as

select
	P01.PIN,
	p01.client as numorgid,
	p01.employmentstartdate, -- as 'Employment Start',
	p01.employmentenddate, -- as 'Employment End',
	P01.Name,
	org.NAME 'Company',
	p03.PAYSCALETABLE,
	p03.PAYSCALE,
	act.TEXT as Activity,
	orgves.NAME 'Vessel',
	P03.Datefrom as 'Date From',
	P03.Todateestimated as 'Estimated End Date',
	case when ((p03.DAteto='' OR Dateto is NULL) AND p03.PLANNED='Y') THEN 'Planned'
		 when ((p03.DAteto='' OR Dateto is NULL) AND p03.PLANNED='N') THEN 'Current' 
		 end
	as 'Activity status',
	case when ((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y') THEN 'Unconfirmed Date From'
		 when ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL)) THEN 'Unconfirmed Date To' 
		 end
	as 'Issue'
	
from PW001P01 P01

left join Pw001P03 P03 on P03.PIN=P01.PIN
left join Pw001C12 act on act.code=p03.code
LEFT JOIN PWORG org ON org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 2)
LEFT JOIN PWORG orgves ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
join
PWPSC000
PT on where (((P03.PAYSCALETABLEDATEFROM <= pt.REGULATIVECODE 
join PWPSC001 PS on P03.PAYSCALE = PS.PAYSCALECODE and PS.SEQNO = pt.SEQNO

where GETDATE()) AND p03.PLANNED='Y') 
OR ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL)))
AND act.code='VAC'
AND  ((pt.ACTIVITYCODES like '%VAC%') and ps.ACTIVITYCODES IS NULL)
     OR (ps.ACTIVITYCODES like '%VAC% in ('VAC')