| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Page Properties | ||||||
|---|---|---|---|---|---|---|
|
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.
...
| Code Block |
|---|
CREATE VIEW dbo.PW001SRV22VIEWID 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 where (((P03.PAYSCALETABLEDATEFROM <= pt.REGULATIVECODE join PWPSC001 PS on P03.PAYSCALE = PS.PAYSCALECODE and PS.SEQNO = pt.SEQNO where GETDATE()) AND p03.PLANNED='Y') OR ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR Dateto is NULL))) AND act.code='VAC' ANDin ((pt.ACTIVITYCODES like '%VAC%') and ps.ACTIVITYCODES IS NULL) OR (ps.ACTIVITYCODES like '%VAC%'VAC') |
View Sample
...