Versions Compared

Version Old Version 2 New Version Current
Changes made by

Anna Zelenskaya

Anna Zelenskaya

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
Show if
groupIdse45c9fb3-68ab-4c08-972a-bdd4f555637e
users
matchUsingany

This section is only visible for the Adonis employees.

Customer

The view was originally created for Eckero Group

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

...

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

sql
Expand
Code Block
language
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
PIN
Select from Personal Details > Personal > Pin
PIN
Employment Start Date
EMPLOYMENT START DATE
Select from Personal Details > Employment > Employment State > Start
Format dd/mm/yyyy
Employment End Date
EMPLOYMENT END DATE
Select from Personal Details > Employment > Employment State > End
Format dd/mm/yyyy
Name
Full name FIRST NAME
FirstName of the person in from Personal Details .
Format: L_F_M
Rank
Current Rank of the person in Personal Details.
Competence Code> 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 competence document record linked to the selected crew.
Competence TextDOCUMENT
The name of the competence document record linked to the selected crew.
Competence Number
The number NUMBER
Nr. of the competence document record linked to the selected crew.
Issued Date
The date issued from of the competence document record linked to the selected crew.
Expiry Date
The expiry date of the competence document record linked to the selected crew.
Last Updated
Date / time last update action was done to this competence document
Last Updated By
Login name who did last update action to this competence document
Validity of Scan
'N'
Start Page
...
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 (no medicals) Tile