CREATE VIEW dbo.VIEWID as

select
	P01SELECT 
       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 asName,
       p01.Firstname  'First Name',
       p01.Lastname  'Last Name',
	   ORG.NAME 'ORG. NAME',
       c12plan.TEXT  'Activity',
       case when p03plan.PLANNED='Y' then 'Planned' else 'Current' end as 'Act.State',
       p03plan.DATEFROM  'Start Date',
       p03plan.TODATEESTIMATED  '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 orgplan_pos.NAME  Position,
       plan_ves.NAME Vessel,
       P01.client NUMORGID,
       p01.employmentstartdate,
       p01.employmentenddate
FROM   PW001P01 p01

       JOIN PW001P03 p03plan
            ON  p03plan.PIN = p01.PIN
       LEFT JOIN PW001C12 c12plan
            ON  c12plan.CODE = p03plan.CODE

       LEFT JOIN PWORG plan_ves
            ON  plan_ves.NUMORGID = dbo.ad_scanorgtree(p03p03plan.NUMORGID, 23)

       LEFT JOIN PWORG orgves plan_pos
            ON  orgvesplan_pos.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
join PWPSC000 PT on P03.PAYSCALETABLE = pt.REGULATIVECODE 
join PWPSC001 PS on P03.PAYSCALE = PS.PAYSCALECODE and PS.SEQNO = pt.SEQNO

where ((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%')p03plan.NUMORGID
            AND plan_pos.ORGTYPE = 5

		LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT

where (p03plan.PLANNED='Y' and p03plan.datefrom= GETDATE ()) 
	or (p03plan.datefrom <= getdate () and p03plan.dateto is NULL and p03plan.todateestimated =  getdate ())
	AND act.code='%VAC%'