Description
Shows all the crew with the vacation activity were either sign on or sign off are were not confirmed in time.
Unconfirmed Date From: the activity status is still planned, but the Date From is already in the past.
Unconfirmed Date To: the activity status is still planned/ current, but the Date To is empty/ Null.
View Sample
SQL Statement
VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV20 etc.
The view is currently setup to show any activity with the code containing ‘VAC’. It is hard-coded in the following condition ps.ACTIVITYCODES like '%VAC%' at the end of the view. If you have a vacation activity with the code without ‘VAC’ you need to adjust the selection. You can consult with support on how to do this.
CREATE VIEW dbo.VIEWID as
SELECT
p01.PIN,
p01.Name,
p01.Firstname 'First Name',
p01.Lastname 'Last Name',
ORG.NAME 'ORG. NAME',
c12plan.TEXT 'Activity',
case when p03plan.PLANNED='Y' then '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.NUMORGID = dbo.ad_scanorgtree(p03plan.NUMORGID, 3)
LEFT JOIN PWORG plan_pos
ON plan_pos.NUMORGID = p03plan.NUMORGID
AND plan_pos.ORGTYPE = 5
LEFT JOIN PWORG ORG ON ORG.NUMORGID=P01.CLIENT
where (p03plan.PLANNED='Y' and p03plan.datefrom= GETDATE ())
or (p03plan.datefrom <= getdate () and p03plan.dateto is NULL and p03plan.todateestimated = getdate ())
AND act.code='%VAC%'