Skip to end of banner
Go to start of banner

Unconfirmed Sign On/ Off

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 2 Next »

Description

Shows all the crew with the onboard activity were either sign on or sign off are were not confirmed in time.

Keywords

Sign On/ Off

Category

Crewing Views

Description

The view shows all the crew with the onboard activity were either sign on or sign off are were not confirmed in time.

  • Unconfirmed Sign On: the activity status is still planned, but the Date From is already in the past.

  • Unconfirmed Sign Off: the activity status is still planned/ current, but the Date To is empty/ Null

Selection

VIEWID should be substituted with the id of the crew list view that you are creating in your system e.g. PW001SRV20 etc.

CREATE VIEW dbo.VIEWID as

select
	P01.PIN,
	p01.client as numorgid,
	p01.employmentstartdate, 
	p01.employmentenddate, 
	P01.Name,
	org.NAME 'Company',
	act.TEXT as Activity,
	orgves.NAME as 'Vessel',
	P03.Datefrom as 'Date From',
	porton.NAME as 'Sign ON Port',
	P03.Todateestimated as 'Estimated End Date',
	portoff.name as 'Sign OFF Port',
	case when ((p03.DAteto='' OR p03.Dateto is NULL) AND p03.PLANNED='Y') THEN 'Planned'
		 when ((p03.DAteto='' OR p03.Dateto is NULL) AND p03.PLANNED='N') THEN 'Current' 
		 end
	as 'Activity status',
	case when ((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y') THEN 'Unconfirmed Sign ON'
		 when ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR p03.Dateto is NULL)) THEN 'Unconfirmed Sign OFF' 
		 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)
LEFT JOIN PWCCMCHANGECREWLIST signon ON signon.NEXTACT = p03.SEQUENCENO 
LEFT JOIN PWCCMCHANGECREWLIST signoff ON signoff.CURRENTACT = p03.SEQUENCENO 
left join PWORGVESACT signonport on signonport.seqno=signon.seqno
left join PWORGVESACT signoffport on signoffport.seqno=signoff.seqno
left join PWPORT porton on porton.PORTCODE=signonport.placefrom
left join PWPORT portoff on portoff.PORTCODE=signoffport.placefrom

where (((P03.DATEFROM <=GETDATE()) AND p03.PLANNED='Y') 
OR ((P03.todateestimated <=GETDATE()) AND (p03.DAteto='' OR p03.Dateto is NULL)))
AND act.OPTIONS like '%S%'

View Sample

  • No labels