...
| Page Properties |
|---|
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
| Expand |
|---|
| Code Block |
|---|
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 |
|---|
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