Versions Compared

Version Old Version 1 New Version Current
Changes made by

Artem Finkelshtein

Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Page Properties
Shows

Description

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

Compatibility APM Version

2021.10

Compatibility SQL Version

2012

Description

Harding crew are supposed to upload most of their documents themselves in the APP. 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

Code Block
languagesql
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