CREATE VIEW dbo.PW001SRV141
as
select 
	p01.PIN,
	P01.ALTERNATIVEPIN                 AS 'Alternative PIN',
 	p01.NAME as 'Full Name',
	p01.FIRSTNAME as 'First Name',
	p01.MIDDLENAME as 'Middle Name',
	p01.LASTNAME as 'Last Name',
	wd.DOCUMENT_ID 'Document ID', 
	wf.APPROVER_PIN 'Approver PIN',  
	wf.STATE_NAME 'State Name',
	tsp.PERIOD 'Period',
	tsp.NAME 'Period Name',
	tsp.PERIOD_FROM 'Period Start',
	tsp.PERIOD_TO 'Period End',
	ts.DATEFROM 'TimeSheet Start',
	case when ts.DATETO is NULL and act.DATETO is null then 'Timesheet is still open'
		 when ts.DATETO is NULL and act.DATETO is not null then 'Timesheet is still open'
		 else convert(varchar, ts.DATETO, 	121)
	end 'TimeSheet End',
	plan_pos.NAME                 Position,
    PLAN_DEP.NAME                 Department,
    plan_ves.NAME                 Vessel,
    plan_ves.NUMORGID             vessel_numorgid,
	p01.client as NUMORGID,	
	p01.EMPLOYMENTSTARTDATE,
	p01.EMPLOYMENTENDDATE
	from WEB_CP_DOCUMENTS wd
left join PW001P01 P01 on 
			P01.PIN=wd.PIN
left join WEB_CP_WORKFLOW wf on 
			wf.DOCUMENT_ID = wd.DOCUMENT_ID 
			and wf.STEP in (select top 1 (step) from WEB_CP_WORKFLOW wf2 where wf2.DOCUMENT_ID=wf.DOCUMENT_ID  order by step desc) 
left join web_cp_timesheets ts on 
			ts.DOCUMENT_ID = wd.DOCUMENT_ID 
			and ts.PIN=wd.PIN
left join WEB_CP_TIMESHEETS_PERIOD TSP on
			TSP.SEQUENCENO=ts.SEQUENCENO
left join PW001P03 ACT on
			act.SEQUENCENO=ts.ONBOARD_ACTIVITY
LEFT JOIN PWORG plan_ves
            ON  plan_ves.NUMORGID = dbo.ad_scanorgtree(act.NUMORGID, 3)
LEFT JOIN PWORG PLAN_DEP
            ON  PLAN_DEP.NUMORGID = dbo.ad_scanorgtree(act.NUMORGID, 4)
LEFT JOIN PWORG plan_pos
            ON  plan_pos.NUMORGID = act.NUMORGID
            AND plan_pos.ORGTYPE = 5
where wd.CODE='HrsReg' and year(tsp.PERIOD_FROM)=Year(Getdate())