Documents uploaded from APP
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