CREATE VIEW dbo.VIEWID as

SELECT 
       p01select
	P01.PIN,
	p01.client as numorgid,
	p01.Nameemploymentstartdate, -- as 'Employment     Start',
	p01.Firstnameemploymentenddate, -- as 'FirstEmployment NameEnd',
       p01.Lastname  'Last Name	P01.Name,
	org.NAME 'Company',
	act.TEXT as Activity,
ORG	orgves.NAME 'ORG. NAMEVessel',
	P03.Datefrom as 'Date From',
	P03.Todateestimated as 'Estimated c12plan.TEXTEnd  Date'Activity',
	case when ((p03.DAteto='' OR Dateto is NULL) caseAND when p03planp03.PLANNED='Y') thenTHEN 'Planned'
else 'Current' end as 'Act.State',
       p03plan.DATEFROM  'Start Date',
       p03plan.TODATEESTIMATED  'Estimated End Date',
       plan_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		 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(p03planp03.NUMORGID, 32)

       LEFT JOIN PWORG plan_pos
            ON  plan_posorgves ON orgves.NUMORGID = p03plandbo.ad_scanorgtree(p03.NUMORGID, 3)

          AND plan_pos.ORGTYPE = 5

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

where (p03planwhere (((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y') and p03plan.datefrom= GETDATE 
OR ((P03.todateestimated <=GETDATE()) 
	orAND (p03planp03.datefrom <DAteto='' getdate () and p03plan.datetoOR Dateto is NULL and p03plan.todateestimated =  getdate ()))
	AND act.code='%VAC%'

 in ('VAC')