Crew List - Timesheets Overview

Owned by Daria Chebotaieva, created
Last updated: Apr 05, 2024
3 min read
1 Description | 2 View Sample | 3 Main Data Selection | 4 SQL statement | 5 Columns Specification | 6 Start Page

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

image-20240405-160426.png

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())

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