Crew List - Documents uploaded from APP

2 min
1 Description | 2 Selection

Summary

The view lists the crew who uploaded documents themselves from APP. The crew list view shows competence, travel, medical, and enclosed documents uploaded from the APP for the last two months.

Keywords

documents, scans

Description

The view lists the crew who uploaded documents themselves from APP.

The crew list view shows competence, travel, medical, and enclosed documents uploaded from the APP for the last two months.

Selection

Select p.PIN, p.CLIENT NUMORGID, p.EMPLOYMENTSTARTDATE, p.EMPLOYMENTENDDATE, p.FIRSTNAME, p.LASTNAME, o.[NAME] Organization, d.[Type], d.Code, d.Document, d.Number, d.[Scan Attached], d.[Scan Valid], d.CREATETIME From PW001P01 p Join (Select c.[PIN], 'Competence' [Type], c.CODE [Code], (Select [TEXT] from PW001C06 where CODE=c.CODE) [Document], c.CNUMBER [Number], Case When c.scanneddocno is not null and Convert(int,c.scanneddocno) in (select docno from PW001OLEDOCS) Then NCHAR(10004) Else '' End [Scan Attached], Case When c.SCANVALIDITY=1 Then NCHAR(10004) Else '' End [Scan Valid], c.[COMMENTS], c.CREATETIME From PW001P05 c where c.CREATEDBY = 'APP' Union All Select tr.[PIN], 'Travel' [Type], tr.CODE [Code], (Select VISATYPE from PW001C23 where CODE=tr.CODE) [Document], tr.TDNUMBER [NUMBER], Case When tr.scanneddocno is not null and Convert(int,tr.scanneddocno) in (select docno from PW001OLEDOCS) Then NCHAR(10004) Else '' End [Scan Attached], Case When tr.SCANVALIDITY=1 Then NCHAR(10004) Else '' End [Scan Valid], tr.[COMMENTS], tr.CREATETIME From PW001P08 tr where tr.CREATEDBY = 'APP' Union All Select m.[PIN], 'Medical' [Type], m.CODE [Code], (Select [TEXT] from PW001C24 where CODE=m.CODE) [Document], 'N/A' [NUMBER], Case When m.scanneddocno is not null and Convert(int,m.scanneddocno) in (select docno from PW001OLEDOCS) Then NCHAR(10004) Else '' End [Scan Attached], Case When m.SCANVALIDITY=1 Then NCHAR(10004) Else '' End [Scan Valid], m.[COMMENTS], m.CREATETIME From PW001P07 m where m.CREATEDBY = 'APP' Union All Select ole.[PIN], 'Enclosed' [Type], s.DOCTYPE [Code], (Select [NAME] from PW001C45 where CODE=s.DOCTYPE) [Document], 'N/A' [NUMBER], NCHAR(10004) [Scan Attached], 'N/A' [Scan Valid], s.[DESCRIPTION] [COMMENTS], s.CREATETIME From PW001P01OLE ole join PW001OLEDOCS s on ole.DOCNO=s.DOCNO where s.CREATEDBY = 'APP' and s.DOCTYPE!='TALOS_CV') d on d.PIN = p.PIN and d.CREATETIME>=DATEADD(day,-60,getutcdate()) Join PWORG o on p.CLIENT = o.NUMORGID

 

Looking for labels? They can now be found in the details panel on the floating action bar.

Related content