1 Description | 2 View Sample | 3 Selection | 4 Field Specification | 5 Start Page

Summary	The view lists the crew who uploaded documents themselves from APP > Employee Portal. It shows competence, travel and enclosed documents uploaded from the APP for the last two months.
Keywords	Competence / Travel Documents, Scans
Category	Crew List View

Description

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

View Sample

Selection

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

CREATE VIEW dbo.PW001SRV28 AS
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 'Scan has not been approved' 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 'Scan has not been approved' End [Scan Valid],
			tr.[COMMENTS],
			tr.CREATETIME
		From PW001P08 tr
		where tr.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!='MED') d on d.PIN = p.PIN and d.CREATETIME>=DATEADD(day,-60,getutcdate())
Join PWORG o on p.CLIENT = o.NUMORGID

Field Specification

Column	Description/ Location in APM

Column	Description/ Location in APM
PIN	Select from Personal Details > Personal > Pin
EMPLOYMENT START DATE	Select from Personal Details > Employment > Employment State > Start
EMPLOYMENT END DATE	Select from Personal Details > Employment > Employment State > End
FIRST NAME	FirstName of the person from Personal Details > Personal > Name
LAST NAME	LastName of the person from Personal Details > Personal > Name
ORGANIZATION	Company name from Personal Details > Employment > Organization/Company.
TYPE	‘Competence’, ‘Travel’, ‘Enclosed’
CODE	The code of the document record linked to the selected crew
DOCUMENT	The name of the document record linked to the selected crew
NUMBER	Nr. of the document record linked to the selected crew
SCAN ATTACHED	Display tick is there is scan attached to the document in question
SCAN VALID	‘Scan has not been approved’ - if ‘Validity of scan’ check-box is off
CREATE TIME	Date & time document was created in APP by employee/pin

Start Page

New Documents from APP (no medicals) Tile

Adonis HR Reports, Views and Similar

Documents uploaded from APP (no medicals)

Analytics

Description

View Sample

Selection

Field Specification

Start Page

Related content