Description
The view shows all the crew with the travel 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
Selection
VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV20 etc.
In the act.code in ('TRAV', 'TRAV2') line at the end of the view you need to insert the code of the travel activity(s) for the view. You can check the code in the General Codes.
CREATE VIEW dbo.VIEWID as
select
P01.PIN,
p01.client as numorgid,
p01.employmentstartdate, -- as 'Employment Start',
p01.employmentenddate, -- as 'Employment End',
P01.Name,
org.NAME 'Company',
act.TEXT as Activity,
orgves.NAME 'Vessel',
P03.Datefrom as 'Date From',
P03.Todateestimated as 'Estimated End Date',
case when ((p03.DAteto='' OR Dateto is NULL) AND p03.PLANNED='Y') THEN 'Planned'
when ((p03.DAteto='' OR Dateto is NULL) AND p03.PLANNED='N') THEN 'Current'
end
as 'Activity status',
case when ((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y') THEN 'Unconfirmed Date From'
when ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL)) THEN 'Unconfirmed Date To'
end
as 'Issue'
from PW001P01 P01
left join Pw001P03 P03 on P03.PIN=P01.PIN
left join Pw001C12 act on act.code=p03.code
LEFT JOIN PWORG org ON org.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 2)
LEFT JOIN PWORG orgves ON orgves.NUMORGID = dbo.ad_scanorgtree(p03.NUMORGID, 3)
where (((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y')
OR ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL)))
AND act.code in ('TRAV', 'TRAV2')