...
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.
...
| Code Block |
|---|
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',
p03.PAYSCALETABLE,
p03.PAYSCALE,
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)
join PWPSC000 PT on P03.PAYSCALETABLE = pt.REGULATIVECODE
join PWPSC001 PS on P03.PAYSCALE = PS.PAYSCALECODE and PS.SEQNO = pt.SEQNO
where ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL))
AND act.code='VAC'
AND ((pt.ACTIVITYCODES like '%VAC%') and ps.ACTIVITYCODES IS NULL)
OR (ps.ACTIVITYCODES like '%VAC%') |
View Sample
...