Summary	The view gives an overview of the crew members' timesheets within the current year.
Keywords	Crew Portal, Timesheet
Category	Crew List View

Description

The view gives an overview of the crew members' timesheets within the current year.

View Sample

Main Data Selection

All the crew members that have timesheets within the current year.

SQL statement

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()) OR year(tsp.PERIOD_to)=Year(Getdate()))

Columns Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	PIN of the person from the Personal Details.
Alternative PIN	Alternative PIN of the person from the Personal Details.
Full Name	Full name of the person from the Personal Details.
First Name	First name of the person from the Personal Details.
Middle Name	Middle name of the person from Perosonal Details.
Last Name	Last name of the person from the Personal Details.
Document ID	Timesheet Document ID from the Crew Portal.
Approver PIN	Timesheet Document Approver PIN from the Crew Portal.
State Name	Timesheet Document state name from the Crew Portal.
Period	Timesheet Document period from the Crew Portal.
Period Name	Timesheet Document period name.
Period Start	Timesheet Document period start.
Period End	Timesheet Document period end.
Timesheet Start	Timesheet Document Start Date.
Timesheet End	Timesheet Document End Date.
Position	Crew Position from the activity linked to the Timesheet Document
Department	Crew Department from the activity linked to the Timesheet Document
Vessel	Crew Vessel from the activity linked to the Timesheet Document
Vessel_NumorgID	Crew Vessel_NumorgID from the activity linked to the Timesheet Document
Employment Start	Employment Start Date from the Personal Details.
Employment End	Employment End Date from the Personal Details.

Start Page

N/A

Adonis HR Reports, Views and Similar

Onboard: Timesheets Overview

Description

View Sample

Main Data Selection

SQL statement

Columns Specification

Start Page

Related content