Versions Compared

Version Old Version 2 New Version 3
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
PIN
Employment Start Date
Select from Personal Details > Employment > Employment State > Start
Format dd/mm/yyyy
Employment End Date
Select from Personal Details > Employment > Employment State > End
Format dd/mm/yyyy
Name
Full name of the person in Personal Details.
Format: L_F_M
Rank
Current Rank of the person in Personal Details.
Competence Code
The code of the competence document record linked to the selected crew.
Competence Text
The name of the competence document record linked to the selected crew.
Competence Number
The number 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'
...